将两个表的结果合并为一个记录集 [英] Combine results from two tables into one recordset

问题描述

我有两个表项目和内容.

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 table whereby 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.

这篇关于将两个表的结果合并为一个记录集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！