View Full Version : little help needed with a query ...

08-08-2006, 04:34 PM
okay ... i'm not sure what's wrong but i have two queries that i combined into one after learning about more complex select statements ...

here's the first:

SELECT artist.id, artist.name, count(song.id) as songs from artist, song where song.artist=artist.id group by artist.name

and the second:

SELECT artist.id, artist.name, count(album.id) as albums from artist, album where album.artist=artist.id group by artist.name

and then combined:

SELECT artist.id, artist.name, count(song.id) as songs, count(album.id) as albums from artist, song, album where song.artist=artist.id and song.album=album.id group by artist.name

seperately these queries yeild the correct counts of either albums by artists or tracks by artists respectively ... but when combined the query yields an unusually high and incorrect count for the first artist returned and then duplicates those values all the way down the result set for all the other artists. what am i doing wrong. i am sorta new to advanced selects like this so i'm sure i'm overlooking something simple. thanks for any help.

08-08-2006, 10:53 PM
can you show the layout of your tables themselves? If you are not missing a table, you might need to add one. To clarify I'm not sure how you are showing which song belongs to which album.


show create table tablename
for each of your tables and some sample rows from each of those tables.

08-08-2006, 11:16 PM
three tables used ... here's their structure:

id (pk)
name (textual name of album)

id (pk)
name (textual name of artist)

id (pk)
artist (id link to artist table)
album (id link to album table)
name (textual name of song)

08-08-2006, 11:34 PM
ok, caught a little typo and fixed it but i'm still getting a little error ... it seems to be a count() thing ... now it returns the same value for all results of the songs and albums columns ...

id | name | songs | albums
01 | Dashboard Confessional | 13 | 13
02 | Jimmy Eat World | 11 | 11

upon doing my own math i have 13 dashboard songs and 11 jimmy songs ... so am i not allowed to do 2 counts in a query or is there something else i need to do? thanks

08-08-2006, 11:52 PM
You are doing a join on 3 tables. I know why you are getting the results you are getting, but I'm having a hard time describing what is going on. I think if you were to tempoarily change your query to "select *" and display the results, you may realize what your query is doing. I'm having a tough time putting it into words...

08-08-2006, 11:59 PM
i'm gonna give that a try ... joins still have me a little confused and maybe that's why ...

08-09-2006, 12:03 AM
the new (select *) query only returns the first song listed per artist ...

08-09-2006, 12:28 AM
SELECT artist.id, artist.name, count( song.id ) AS songs, count( album.id ) AS albums
FROM artist
RIGHT JOIN song, album ON song.artist = artist.id
AND song.album = album.id
GROUP BY artist.name

rendered the same results as post #4 but added an additional result set at the top ... result:

id | name | songs | albums
NULL | NULL | 120 | 120
01 | Dashboard Confessional | 13 | 13
02 | Jimmy Eat World | 11 | 11

08-09-2006, 12:29 AM
Meh I'm sorry, you'd have to change the "group by" to an "order by" to see what I'm seeing in my head.

The bottom line is you can't count more than one column in a query, the way you were trying to do it.

Instead try select count(distinct ):

SELECT artist.name,
count(distinct song.id) as songs,
count(distinct album.id) as albums
from artist, song, album
where song.artist=artist.id
and song.album=album.id
group by artist.name

I'm 65% sure this will work...

08-09-2006, 12:54 AM
ok, thanks ... i was reading count functions in mysql's documentation and came up with the distinct and tried that (right before you mentioned it) ... result:
01 | Dashboard Confessional | 13 | 1
02 | Jimmy Eat World | 11 | 1

so you get a different number for albums ... but i have 3 albums listed for jimmy in album table and 4 for dashboard. i'm not sure why it turns up 1 ... i will investigate ... thanks again.

08-09-2006, 12:57 AM
found it ... the album id's in album table are placeholders ... i haven't yet added any songs to the song table using those album id's ... duh!
thanks fumigator ...
it seems my query was right up on the first post except adding a distinct to a count.