如何从具有相同ID的多个记录的另一个表中获取记录？
问题描述：
I have table structures as follows
table-1
-------
id
name
table-2
--------
imageId
id ( reference of table1.id)
image
and table1 has the following record
id name
1 A
2 B
3 C
Table 2 has
imageId id image
1 1 image1.jpg
2 1 image2.jpg
3 2 image3.jpg
i.e, table1.id has many images in table2, now i need to select the record and its corresponding images(multiple images for same id), like
id name image
1 A image1.jpg,image2.jpg
2 B image3.jpg
我的表结构如下 p>
table-1 \ n -------
id
name
table-2
--------
imageId
id（table1.id的引用）
image
code>
并且table1具有以下记录 p>
id name
1 A
2 B
3 C
code> pre >
表2有 p>
imageId id image
1 1 image1.jpg
2 1 image2.jpg
3 2 image3.jpg
code> pre>
即table1.id在table2中有很多图像，现在我需要选择记录及其相应的图像（同一个id的多个图像），如 p>
id name image
1 image1.jpg，image2.jpg
2 B image3.jpg
code> pre>
div>
答
You can use the GROUP_CONCAT function to get values across multiple rows into a single comma-delimited string (such as how you have in your example desired result):
SELECT a.id, a.name, GROUP_CONCAT(b.image) AS images
FROM table1 a
INNER JOIN table2 b ON a.id = b.id
GROUP BY a.id, a.name
答
select
b.imageId,
a.id,
b.imageName
from
table-1 a
right outer join table-2
on a.id=b.id
This will give you an output of:
id name image
1 A image1.jpg
1 A image2.jpg
2 B image3.jpg