[SOLVED] Help with Query

[limitphp]

I have two tables:

playlists

columns include:

id, name, userID

 

playlist_songs

columns include:

id, playlistID, songID

 

I want to list all the playlists for userID 2 and I want to show the number of songs in the playlist.

I tried using this query:

SELECT name,COUNT(ps.playlistID) as song_count

FROM playlists p

INNER JOIN playlist_songs ps ON p.id = ps.playlistID

WHERE userID = 'u2'

 

I get this error:

#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

 

I tried putting a GROUP BY song_count in there, but that didn't work either.

Do I need to do something other than using an inner join to get the song count for each playlist?

thanks

 

[limitphp]

ok, now it works....

 

I had to change the order in the inner join:

 

SELECT p.name, count( ps.id ) AS song_count

FROM playlists p

INNER JOIN playlist_songs ps ON ps.playlistID = p.id

WHERE p.userID =2

GROUP BY p.name