Need help with PHP and MySQL…
I have a table of songs, some songs are album song, and some are singles... And I have a table of albums...
Songs table cols: Song_ID, Album_ID, Song_Name, Date_Released, Timestamp_Released
, others...
If the Album_ID is [null]
, it means the song is a single
Albums table cols: Album_ID, Album_Name, Song_IDs, Date_Released
, others...
Note 1: there is no Timestamp_Released
in albums table
Note 2: Date_Released
is only a day with no time i.e. "2011-06-16"
I'm currently using this query to display a table (in my html/php page) that each row is a single or a album (songs that are in an album are displayed all in one row as album)
SELECT
IF(Album_ID IS NULL,s.Song_Name,a.Album_Name) as name,
IF(Album_ID IS NULL,s.Date_Released,a.Date_Released) as datereleased,
s.Timestamp_Released
FROM songs s LEFT JOIN albums a ON (s.Album_ID = a.Album_ID)
GROUP BY 1,2
ORDER BY 开发者_运维问答2 DESC,1
LIMIT 0,10;
The query above order the list of songs and albums according to date and give the albums the Date_Released
and Timestamp_Released
of the oldest song in the album...
So my question is how to give the album the Date_Released
and Timestamp_Released
of the newest song in it ?
Thank you :)
Instead of s.Date_Released, s.Timestamp_Released
write MAX(s.Date_Released) as Newest_Date_Released, MAX(s.Timestamp_Released) as Newest_Timestamp_Releasd
UPDATE
SELECT
IF(Album_ID IS NULL,s.Song_Name,a.Album_Name) as name,
MAX(IF(Album_ID IS NULL,s.Date_Released,a.Date_Released)) as datereleased,
MAX(s.Timestamp_Released)
FROM songs s LEFT JOIN albums a ON (s.Album_ID = a.Album_ID)
GROUP BY 1
ORDER BY 2 DESC,1
LIMIT 0,10;
精彩评论