mySQL: Selecting data from multiple tables

Question

I need help building a mysql query to select from multiple tables. I have three database tables related to images: images, tags, tag_asc. I want to fetch an image data and its tag names by providing image_id.

For example, following is my tables structure:

images:

image_id    image_name     path       date   
1            test.jpg      dir       1311054433

tags:

tag_id      image_id
1             1
2             1

tag_asc:

tag_id      tag_name
1            "first"
2            "second"
3            "third"

I want to fetch the data of an image with image_id = 1 from images table and all tag names associated with image_id=1 from tag_asc table.

I'm using CodeIgniter Active records, but I just need idea of joining the tables.

Thanks for any help.

Bohemian · Accepted Answer · 2011-07-19 12:11:27Z

2

select *
from images i
left join tags t on t.image_id = i.image_id
left join tag_asc ta on ta.tag_id = t.tag_id
where i.image_id = 1;

Using LEFT JOIN means that rows will be returned even if there are no joining rows in the other tables (but you'll get null values in the columns for the missing rows), which is typically desirable.

If you want one row (not stated in question, but in comments), use this:

select i.image_id, group_concat(tag_name) as tag_names
from images i
left join tags t on t.image_id = i.image_id
left join tag_asc ta on ta.tag_id = t.tag_id
where i.image_id = 1
group by 1;

edited Jul 19, 2011 at 12:11

answered Jul 19, 2011 at 10:13

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

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

7 Comments

Roman Over a year ago

Thanks, but as i said 1 image can have multiple rows of tags in table tags (please see my example data). This query returns only one row from the tags table. For example see image_id 1 has two tags but it returns only 1. Can you tell me how to fix it? thanks.

Roman Over a year ago

@Bohemian, Thanks for your reply. Actually your first solution was exactly what I was looking for, but there was some problem in the while loop to fetch the results. Very sorry for that.

Bohemian Over a year ago

@ypercube what's wrong with "group by 1"? That syntax goes back to SQL92 standard.

ypercubeᵀᴹ Over a year ago

@Bohemian: Nothing wrong with it. I was expecting GROUP BY image_id. I hadn't noticed the where restriction to one image only.

ypercubeᵀᴹ Over a year ago

@Bohemian: Yeah I know, i just don't use this feature so it sometimes confuses me when I see it (which usually is in ORDER BY).

|

Collectives™ on Stack Overflow

mySQL: Selecting data from multiple tables

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related