问题描述
我有两个表项目和内容.
I have two tables items and content.
items:|ID|menu|img
表
itemcontent |ID|parent|title|content
内容保全由持有标题和内容的父母与项目配对
content holds is paired to items by parent holding the title and content
我想搜索所有项目并打印出那些在itemcontent表中没有标题的记录标题将被打印为空".所以打印输出看起来像这样:
i want to search all the items and also print out those records wich do not have a title present in the itemcontent tablewhereby the titles will be printed as "Empty".so printing out the output would look something like:
title: test1 and ID: items.ID=1
title: Empty and ID: items.ID=2
title: Empty and ID: items.ID=3
title: test2 and ID: items.ID=4
title: Empty and ID: items.ID=5
etc...
我尝试了以下操作,然后尝试了一些但无济于事:
I tried the following and then some but to no avail:
SELECT items.*, itemcontent.title, itemcontent.content
FROM items, itemcontent
WHERE itemcontent.title LIKE '%$search%'
AND itemcontent.parent = items.ID
order by title ASC
一点帮助将不胜感激
推荐答案
由于您希望项目中的所有行(无论它们在itemcontent中是否匹配),以及要从itemcontent中获得的字段(如果存在匹配项),都需要使用外加入:
Since you want all the rows from items whether or not they have a match in itemcontent, plus a field from itemcontent when there is a match you need to use an OUTER JOIN:
SELECT items.*, COALESCE(itemcontent.title, 'empty'), itemcontent.content
FROM items LEFT OUTER JOIN itemcontent ON itemcontent.parent = items.ID
WHERE (itemcontent.title LIKE '%$search%' OR itemcontent.title IS NULL)
ORDER BY items.ID, itemcontent.title ASC
SQL方言之间的差异很小(例如,并非所有版本都具有COALESCE),因此,如果您想要更精确的答案,请指明您使用的是哪种产品.
There are small differences among SQL dialects (for instance, not all versions have COALESCE) so if you want a more precise answer indicate which product you're using.
这篇关于将两个表的结果合并为一个记录集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!