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-07 14:48:23Z

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 7, 2015 at 14:48

answered Oct 7, 2015 at 14:19

zedfoxus

37.4k5 gold badges68 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

ion Over a year ago

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.

zedfoxus Over a year ago

I have added an extension to the answer. Is that what you were looking for @ion?

ion Over a year ago

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.

zedfoxus Over a year ago

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 and use it as an object in PHP.

ion Over a year ago

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?

|

Collectives™ on Stack Overflow

Mysql table join with multiple rows

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related