Hi, I have three tables:
**work **- contains details on each artwork; id, date, title, description, type
**albums **- contains album names; id, album
**wa **- the relationship table; work_id, album_id
For a page in my gallery, I need to display a list of albums with thumbnail image of the most recent work in each album as the album’s cover art.
This is the query I’m using:
SELECT a.id AS album_id, a.album AS album_name, w.id AS work_id
FROM albums AS a, work AS w, wa AS r
WHERE r.album_id = a.id AND r.work_id = w.id GROUP BY a.id
However, it does not return the most recent records (which is right, because I haven’t told it anything about dates), and what I don’t know is what I should write and where it should be.
It returns:
“album_id”,“album_name”,“work_id”
1,“sketches”,1
2,“paintings”,2
3,“collaborations”,3
5,“temp”,5
But in the temp album, there’s a more recent artwork with an id of 6.
Please help me, I tried looking in other places but none of the examples I found matched my situation and I just can’t figure it out.
Thank you for your time,
Michael Popov
