我们有表格页面,我们想要在pages.uid = tableX.pid上连接4个不同的表格,并且仅在例如tableX.ext_url与LIKE匹配时才需要这些结果。我已经写了以下查询,但是它仍然返回值,因为所有“ LIKE”值都为空-且结果太多了
SELECT pages.uid, pages.title, tt_news.ext_url, tt_content.bodytext, tx_mask_facts.tx_mask_link, tx_mask_links.tx_mask_link FROM pages
LEFT JOIN tt_news ON pages.uid = tt_news.pid AND tt_news.ext_url LIKE '%//media.%' AND tt_news.deleted <> 1 AND tt_news.hidden <> 1
LEFT JOIN tt_content ON pages.uid = tt_content.pid AND tt_content.bodytext LIKE '%//media.%' AND tt_content.deleted <> 1 AND tt_content.hidden <> 1
LEFT JOIN tx_mask_facts ON pages.uid = tx_mask_facts.pid AND tx_mask_facts.tx_mask_link LIKE '%//media.%' AND tx_mask_facts.deleted <> 1 AND tx_mask_facts.hidden <> 1
LEFT JOIN tx_mask_links ON pages.uid = tx_mask_links.pid AND tx_mask_links.tx_mask_link LIKE '%//media.%' AND tx_mask_links.deleted <> 1 AND tx_mask_links.hidden <> 1
WHERE
pages.deleted <> 1 AND pages.hidden <> 1
GROUP BY pages.uid;
我怎样才能做到这一点?
另一种尝试:
SELECT pages.uid, pages.title FROM pages
LEFT JOIN tt_news ON pages.uid = tt_news.pid
LEFT JOIN tt_content ON pages.uid = tt_content.pid
LEFT JOIN tx_mask_facts ON pages.uid = tx_mask_facts.pid
LEFT JOIN tx_mask_links ON pages.uid = tx_mask_links.pid
WHERE
pages.deleted <> 1 AND pages.hidden <> 1
AND
tt_news.deleted <> 1 AND tt_news.hidden <> 1
AND
tt_content.deleted <> 1 AND tt_content.hidden <> 1
AND
tx_mask_facts.deleted <> 1 AND tx_mask_facts.hidden <> 1
AND
tx_mask_links.deleted <> 1 AND tx_mask_links.hidden <> 1
AND
tt_news.ext_url LIKE '%media.%'
AND
tt_content.bodytext LIKE '%media.%'
AND
tx_mask_facts.tx_mask_link LIKE '%media.%'
AND
tx_mask_links.tx_mask_link LIKE '%media.%'
GROUP BY
pages.uid;
另一个尝试:
SELECT pages.uid, pages.title, tt_news.ext_url, tt_content.bodytext, tx_mask_facts.tx_mask_link, tx_mask_links.tx_mask_link FROM pages
LEFT JOIN tt_news ON pages.uid = tt_news.pid AND tt_news.ext_url LIKE '%//media.%' AND tt_news.deleted <> 1 AND tt_news.hidden <> 1
LEFT JOIN tt_content ON pages.uid = tt_content.pid AND tt_content.bodytext LIKE '%//media.%' AND tt_content.deleted <> 1 AND tt_content.hidden <> 1
LEFT JOIN tx_mask_facts ON pages.uid = tx_mask_facts.pid AND tx_mask_facts.tx_mask_link LIKE '%//media.%' AND tx_mask_facts.deleted <> 1 AND tx_mask_facts.hidden <> 1
LEFT JOIN tx_mask_links ON pages.uid = tx_mask_links.pid AND tx_mask_links.tx_mask_link LIKE '%//media.%' AND tx_mask_links.deleted <> 1 AND tx_mask_links.hidden <> 1
WHERE
pages.deleted <> 1 AND pages.hidden <> 1
AND
tt_news.ext_url IS NOT NULL
AND
tt_content.bodytext IS NOT NULL
AND
tx_mask_facts.tx_mask_link IS NOT NULL
AND
tx_mask_links.tx_mask_link IS NOT NULL
GROUP BY pages.uid;
仍然没有成功
根据建议:
SELECT pages.uid, pages.title, tt_news.ext_url, tt_content.bodytext, tx_mask_facts.tx_mask_link, tx_mask_links.tx_mask_link FROM pages
LEFT JOIN tt_news ON pages.uid = tt_news.pid AND tt_news.ext_url LIKE '%//media.%' AND tt_news.deleted <> 1 AND tt_news.hidden <> 1 AND tt_news.pid IS NOT NULL
LEFT JOIN tt_content ON pages.uid = tt_content.pid AND tt_content.bodytext LIKE '%//media.%' AND tt_content.deleted <> 1 AND tt_content.hidden <> 1 AND tt_content.pid IS NOT NULL
LEFT JOIN tx_mask_facts ON pages.uid = tx_mask_facts.pid AND tx_mask_facts.tx_mask_link LIKE '%//media.%' AND tx_mask_facts.deleted <> 1 AND tx_mask_facts.hidden <> 1 AND tx_mask_facts.pid IS NOT NULL
LEFT JOIN tx_mask_links ON pages.uid = tx_mask_links.pid AND tx_mask_links.tx_mask_link LIKE '%//media.%' AND tx_mask_links.deleted <> 1 AND tx_mask_links.hidden <> 1 AND tx_mask_links.pid IS NOT NULL
WHERE
pages.deleted <> 1 AND pages.hidden <> 1
GROUP BY pages.uid;
但结果还是一样
最终查询-工作示例:
SELECT pages.uid, pages.title, tt_news.ext_url, tt_content.bodytext, tx_mask_facts.tx_mask_link, tx_mask_links.tx_mask_link FROM pages
LEFT JOIN tt_news ON pages.uid = tt_news.pid AND tt_news.ext_url LIKE '%//media.%' AND tt_news.deleted = 0 AND tt_news.hidden = 0
LEFT JOIN tt_content ON pages.uid = tt_content.pid AND tt_content.bodytext LIKE '%//media.%' AND tt_content.deleted = 0 AND tt_content.hidden = 0
LEFT JOIN tx_mask_facts ON pages.uid = tx_mask_facts.pid AND tx_mask_facts.tx_mask_link LIKE '%//media.%' AND tx_mask_facts.deleted = 0 AND tx_mask_facts.hidden = 0
LEFT JOIN tx_mask_links ON pages.uid = tx_mask_links.pid AND tx_mask_links.tx_mask_link LIKE '%//media.%' AND tx_mask_links.deleted = 0 AND tx_mask_links.hidden = 0
WHERE
pages.deleted <> 1 AND pages.hidden <> 1 AND (tt_news.ext_url IS NOT NULL OR tt_content.bodytext IS NOT NULL OR tx_mask_facts.tx_mask_link IS NOT NULL OR tx_mask_links.tx_mask_link)
GROUP BY pages.uid;
所以问题是在LIKE值上带有嵌套OR的where子句之后缺少AND
对不起,我的英语不好
最佳答案
这将起作用:
编写另一个条件检查AND tableX.pid IS NOT NULL
关于mysql - MySQL Multiple JOINS,其中至少一个LIKE包含一个值,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/56274672/