CODEWITHSUNDEEP
phpmysqlsql
shox
shox

Reputation: 1160

Get a picture for each album

I have two table for gallery system : 

gallery_cat(
  gallery_cat_id    PK,
  gallery_cat_name
)

gallery(
  gallery_id        PK,
  gallery_cat_id    FK,
  gallery_name,
  gallery_file_name,
  gallery_date
)

I need to write a SQL query that return one picture from gallery table for each album, the purpose of this that I need to list the albums with one picture for each. 

gallery_name | gallery_cat_name| gallery_file_name
-------------+-----------------+------------------
pic1         | Album1          | pic1.jpg

Upvotes: 2

Views: 130

Answers (3)

Johan
Johan

Reputation: 76557

This should do the trick:

SELECT g2.gallery_name, gc2.gallery_cat_name, g2.gallery_file_name
FROM gallery g2
INNER JOIN gallery_cat gc2 ON (g2.gallery_cat_id = gc2.gallery_cat_id)
WHERE g2.gallery_id IN (
  SELECT g.gallery_id     
  FROM gallery g
  GROUP BY g.gallery_cat_id)

Explanation:

At the end is a sub-select 

IN (
  SELECT g.gallery_id     
  FROM gallery g
  GROUP BY g.gallery_cat_id) <<-- select 1 random g.id per gallery_cat.

Here I select all g.id, but because of the group by clause it will reduce the results to 1 row per grouped by item. I.e. 1 row (chosen more or less at random) per g.gallery_cat_id.

Next I do a normal select with a join:

SELECT g2.gallery_name, gc2.gallery_cat_name, g2.gallery_file_name
FROM gallery g2
INNER JOIN gallery_cat gc2 ON (g2.gallery_cat_id = gc2.gallery_cat_id)
WHERE g2.gallery_id IN (

Because I refer to the same table twice in the same query you have to use an alias(*).
I select all names and all catnames and all filenames.
However in the where clause I filter these so that only rows from the sub-select are shown.

I have to do it this way, because the group by mixes rows into one messed up ow, if I select from that directly I will get values from different rows mixed together, not a good thing.
By first selecting the id's I want and then matching full rows to those id I prevent this from happening.

*(in this case with this kind of subselect that's not really 100% true, but trust me on the point that it's always a good idea to alias your tables)

Upvotes: 2

Michael Berkowski
Michael Berkowski

Reputation: 270607

This attempts to select the most recent gallery_date for each category ID and join against gallery_cat

SELECT 
  c.gallery_cat_id, 
  c.gallery_cat_name, 
  i.lastimg
FROM
  gallery_cat c 
  LEFT JOIN (
   SELECT gallery_cat_id, gallery_filename AS lastimg, MAX(gallery_date)
   FROM gallery 
   GROUP BY gallery_cat_id, gallery_filename 
  ) i ON c.gallery_cat_id = i.gallery_cat_id

Upvotes: 1

Seralize
Seralize

Reputation: 1127

You can use SQL JOINS to do this, otherwise you would have to loop out all the albums and pick one random picture from each which would be less efficient.

Upvotes: 0

Related Questions