...

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



zick
08-08-2006, 05: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.

guelphdad
08-08-2006, 11: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.

Use:

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

zick
08-09-2006, 12:16 AM
three tables used ... here's their structure:

album
====
id (pk)
name (textual name of album)

artist
====
id (pk)
name (textual name of artist)

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

zick
08-09-2006, 12:34 AM
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 ...

result:
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

Fumigator
08-09-2006, 12:52 AM
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...

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

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

zick
08-09-2006, 01: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

Fumigator
08-09-2006, 01: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...

zick
08-09-2006, 01: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.

zick
08-09-2006, 01: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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum