SQL (LEFT JOIN) (photo, category)

Question

I have three tables:

Photos:

photo_id
photo_name

Photo_to_category

id
photo_id
category_id

Category

category_id
category_name

I would like show all photo with category. One photo can have eg.. 5 category. Someone can get me a full SQL request? I try with LEFT JOIN but it not working too well. Maybe GROUP()?? Thanks

// MY CODE

SELECT * FROM photos AS p
LEFT JOIN photo_to_category AS ptc ON p.photo_id = ptc.photo_id
LEFT JOIN category AS c ON ptc.category_id = c.category_id

I suggest you read [this article](http://whathaveyoutried.com) — Barranka, Jan 21 '14 at 22:47
Could you also post an example of your query result? I am pretty sure I know what is wrong with your query, but I would need to see a result to be sure. Also, if you post what kind of array or object you want as an end result, that would be good too. — pathfinder, Jan 21 '14 at 22:55
Returns me a couple of times the same picture but with another category. — Kamilos, Jan 21 '14 at 23:00
I know, but i would like get something like [category] => array(one, two) etc. — Kamilos, Jan 21 '14 at 23:03
Because problem is with LIMIT eg. photo have a three category, when I add LIMIT 1, I get only one photo and one category... — Kamilos, Jan 21 '14 at 23:05

score 0 · Accepted Answer · edited May 23 '17 at 10:25

0

You have to use GROUP_CONCAT to generate a string that contains all categories of a photo, and group your results by photo id.

See this question for more information: How to use GROUP BY to concatenate strings in MySQL?

edited May 23 '17 at 10:25

Community

1
1

answered Jan 21 '14 at 23:03

Fabien Warniez

2,731
1
21
30

I have another question :) I would like to get all the photos from the category and display a rest category (eg a picture of the city may be in the category of the night, the city, people) Should I get a picture and categories. WHERE c.category_id = 14 / / does not work, I get the photo but without the rest of the category – Kamilos Jan 21 '14 at 23:14
You need to join the category table and the joint table twice (under different names). One is used to filter (the WHERE), one is used to retrieve the categories to the images. – Fabien Warniez Jan 21 '14 at 23:19
Can you get me example? – Kamilos Jan 21 '14 at 23:26
LEFT JOIN the join table twice (ie. copy paste the line you have and change the alias to ptc2). Then use ptc2 to filter (in the WHERE). – Fabien Warniez Jan 21 '14 at 23:28

SQL (LEFT JOIN) (photo, category)

1 Answers1