mysql - MySQL Multiple JOINS 至少有一个 LIKE 包含一个值

Question

我们有表格页面，如果我们想在 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;

所以问题是在 where 子句之后缺少 AND 与 LIKE 值上的嵌套 OR）

对不起我的英语不好

标签： mysql

Answer 1

这将起作用：

编写另一个条件检查AND tableX.pid IS NOT NULL