Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    New Coder
    Join Date
    Jul 2006
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    little help needed with a query ...

    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:

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

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

    Code:
    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.
    Last edited by zick; 08-08-2006 at 11:26 PM.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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:
    Code:
    show create table tablename
    for each of your tables and some sample rows from each of those tables.

  • #3
    New Coder
    Join Date
    Jul 2006
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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)

  • #4
    New Coder
    Join Date
    Jul 2006
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #5
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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...

  • #6
    New Coder
    Join Date
    Jul 2006
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i'm gonna give that a try ... joins still have me a little confused and maybe that's why ...

  • #7
    New Coder
    Join Date
    Jul 2006
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    the new (select *) query only returns the first song listed per artist ...

  • #8
    New Coder
    Join Date
    Jul 2006
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #9
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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 ):
    Code:
    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...

  • #10
    New Coder
    Join Date
    Jul 2006
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #11
    New Coder
    Join Date
    Jul 2006
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •