Mysql table join with multiple rows

Question

I have a table called works and a table called images. A single work can have multiple images. I have a work_id in table images which links to the id of table works. I am trying to build a single query to fetch all works and all related images of each work.

At the moment I can get all works however only 1 row from the images table. Here is the query.

SELECT w.id, w.title, i.imPath
FROM works w
LEFT JOIN images i ON w.id=i.work_id
WHERE w.isActive=1
GROUP BY w.id
ORDER BY w.ordr

Any help on how to get all rows on images tables for each work?

Thank you

zedfoxus · Accepted Answer · 2015-10-07T14:48:23.447

3

Remove the GROUP BY.

SELECT w.id, w.title, i.imPath
FROM works w
LEFT JOIN images i ON w.id=i.work_id
WHERE w.isActive=1
ORDER BY w.ordr

That should give you all rows you desire.

Another way to look at it:

create table works(id int, title varchar(10));
insert into works values (1, 'hello'), (2, 'world');

create table images(id int, work_id int, impath varchar(100))
insert into images values (1, 1, '/data/hello1.png'), (2, 1, '/data/hello2.png'), (3, 2, '/data/world1.png');

Works

id | title
1  | hello
2  | world

Images

id | work_id | impath
1  | 1       | /data/hello1.png
2  | 1       | /data/hello2.png
3  | 2       | /data/world1.png

Which can be translated to something like this in your case:

SELECT w.id, w.title, 
       GROUP_CONCAT(DISTINCT i.imPath ORDER BY i.imPath SEPARATOR ',') AS images
FROM works w
LEFT JOIN images i ON w.id=i.work_id
WHERE w.isActive=1
GROUP BY w.id, w.title
ORDER BY w.ordr

Result:
id | title | images
1  | hello | /data/hello1.png,/data/hello2.png
2  | world | /data/world1.png

SQLFiddle example: http://sqlfiddle.com/#!9/b5784c/1

To get caption of the image as well, you could do something like this:

SELECT w.id, w.title, 
       GROUP_CONCAT(
          DISTINCT CONCAT(i.imPath,'|',i.caption) 
          ORDER BY CONCAT(i.imPath,'|',i.caption) 
          SEPARATOR ',') AS images
FROM works w
LEFT JOIN images i ON w.id=i.work_id
WHERE w.isActive=1
GROUP BY w.id, w.title
ORDER BY w.ordr

edited Oct 07 '15 at 14:48

answered Oct 07 '15 at 14:19

zedfoxus

35,121
5
64
63

Yes you are right however what I want to achieve is to get for each work multiple images. If I remove the group by then I get the work data multiple times. – ion Oct 07 '15 at 14:26
I have added an extension to the answer. Is that what you were looking for @ion? – zedfoxus Oct 07 '15 at 14:35
Thats great!! Is there a way to get images as an array rather than a string? I mean I could do that later with php but I would like to achieve as much as as possible with mysql. – ion Oct 07 '15 at 14:38
I don't think MySQL has arrays comparable to PHP arrays. I'd recommend using PHP to split group_concat'ed field by ',' to get a well-usable PHP array. You could convert the group_concat'ed [field to JSON](http://stackoverflow.com/questions/12511933/how-create-json-format-with-group-concat-mysql) and use it as an object in PHP. – zedfoxus Oct 07 '15 at 14:41
Great. One last thing. I also have a field in the images table called caption. How can I get that as well. At the moment if I add it to the concat function it adds it next to the imPath. Is there a way to get it as a separate field? – ion Oct 07 '15 at 14:44
I've added an example of doing that. You would extract the images column in PHP, split by `,` to get individual image-caption combination and split image-caption combination by '|' to get image and caption. – zedfoxus Oct 07 '15 at 14:49

Mysql table join with multiple rows

1 Answers1