[SOLVED] Need Help With a Count

[limitphp]

I have a table

playlist_songs

columns include:

id, playlistID, songID

 

I want to select all the playlists that songID '1' is in and count the number of songs in each playlist.  Is this possible with 1 query?

 

Right now I'm doing this:

SELECT COUNT( id )

FROM playlist_songs

WHERE songID = '1'

GROUP BY playlistID

 

But its giving me the wrong count for each playlist.

 

[sasa]

try

SELECT a.playlistID, COUNT( b.id )
FROM playlist_songs as a
LEFT JOIN playlist_songs as b
ON a.playlistID=b.playlistID
WHERE a.songID = '1'
GROUP BY a.playlistID

not tested

 

[limitphp]

try

SELECT a.playlistID, COUNT( b.id )
FROM playlist_songs as a
LEFT JOIN playlist_songs as b
ON a.playlistID=b.playlistID
WHERE a.songID = '1'
GROUP BY a.playlistID

not tested

 

 

It worked!

thank you.....

 

Thats wierd, left joining the same table with itself.  I'm learning more everyday.

thanks again.