how to select distinct rows from two tables linked with foreign key

Question

I want to show images from database.I have two tables gallery_photos

gallery_category

I want to show any photos_filename with category_name. photo_category is the foreign key of category_id

mysql query i wrote

SELECT distinct a.category_name
       ,b.photo_filename 
FROM gallery_category a  
inner join gallery_photos b  on (a.category_id=b.photo_category)

please help me to do that...

i want to select exactly like this

albums/1456226111.jpg interior
albums/1456226239.jpg graphics
albums/1456226339.jpg random
albums/1456226478.jpg goods

albums/1456226122.jpg albums/1456226153.jpg albums/1456226168.jpg this images belogs from interior category. so why you write in graphics, random and goods ? — Vipin Jain, Feb 24 '16 at 10:00

score 2 · Accepted Answer · answered Feb 24 '16 at 10:04

2

you can grouping it by group by

SELECT a.category_name,b.photo_filename 
FROM gallery_category a  JOIN gallery_photos b  
ON a.category_id=b.photo_category 
GROUP BY a.category_name

answered Feb 24 '16 at 10:04

Vipin Jain

@SibyXavier its my pleasure. – Vipin Jain Feb 24 '16 at 10:08
1

you sure it's working? b.photo_filename Column is not contained in either an aggregate function or the GROUP BY clause – LKW Feb 24 '16 at 10:13

score 1 · Answer 2 · edited May 23 '17 at 12:19

1

Use Left join to keep all the rows in the left table.

SELECT 
  b.photo_filename, 
  a.category_name 
FROM 
  gallery_category a  LEFT join gallery_photos b  ON (a.category_id=b.photo_category)

check this out!

edited May 23 '17 at 12:19

Community

answered Feb 24 '16 at 10:02

LKW

1

its show all image from all category but he want only one image per category am i right ? @SibyXavier – Vipin Jain Feb 24 '16 at 10:05
but what if there is more than one image in the category?what is the criteria to pick ? – LKW Feb 24 '16 at 10:08

2 Answers2