#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    16
    Rep Power
    0

    trouble with DISTINCT


    I have a database in access that has a lot of tables linked together with keys. I have them linked in coldfusion with a recordset like this:

    SELECT Title.TitleID, Title.Title, Title.GenreID, Genre.Genre, Genre.GenreID,
    artist.Artist, artist.ArtistID, Title.AlbumID, album.AlbumID, album.Album
    FROM Title, Genre, artist, album
    WHERE Title.GenreID=Genre.GenreID AND Title.ArtistID=artist.ArtistID
    AND Title.AlbumID=album.AlbumID

    When I use this, and I want just all of my artists to come up in a dynamic table, I get all of the titles, all of the artists, and all of the artists. I want to use this recordset in all of my pages, first page being artist, then album, then titles. If I do a SELECT DISTINCT, it does nothing. Besides, I just want to select the distinct artist in this case, and I need this recordset to link the page to the album page by passing the unique key of TitleID. Any ideas?
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,265
    Rep Power
    968
    Probably the reason adding DISTINCT to your query doesn't have any impact is because each combination of title, genre, artist and album is already distinct. Distinct only works when there are duplicates for the combination of all the fields in the query.

    I think you are not sure what you want the query do do. How can you get only 1 artist from the query, but still get all of the albums?

    Perhaps what you really want to do is order the query by artist, and then when you output the data use <cfoutput query="myquery" group="artist">? This would allow you to show the artist name 1 time, but under that output all of the albums for that artist.

    Hope that helps.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    16
    Rep Power
    0
    Actually that might help for my album page, after you click the artist's name. I want a list of all of the artitst under a certain letter, which I can do, but when I get all of the artists, I get one artist for every song. I want just a single listing for every artist. I can't really figure it out like this, but I have come across a different way to do it, just by making a dynamic table and linking to my album page and filtering it by the artist. I am getting my same effect, only I was hoping to use the same recordset for each page. Could anyone touch a little more on the grouping? That sounds like what I want for my album page
  6. #4
  7. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,265
    Rep Power
    968
    If you only want one artist in the query, regardless of how many actual albums they have in the database, you just need to pare down the query:

    SELECT
    artist.Artist, artist.ArtistID
    FROM artist


    Assuming each artist in the artist table is unique (has it's own unique key) this should just give you 1 row for each artist. If you want to only do this for 1 letter, you can do:

    SELECT
    artist.Artist, artist.ArtistID
    FROM artist
    WHERE artist LIKE '#url.chosenLetter#%'

    You could also use an SQL string function like Left() to match the letter but this is a bit more simple and cross-platform, though it is also slower. But if there aren't more than a few thousand artists the difference between using LIKE and using Left() would be small.
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    16
    Rep Power
    0
    That's exactly what I have done on my artist page. Now I need to know how do I use "group", just for my album page, so I can group all of the songs from one artist into just displaying the one album? Each artist will have a few albums.
    Last edited by DoocesWild22; January 22nd, 2004 at 07:49 PM.
  10. #6
  11. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,265
    Rep Power
    968
    Well if you want to output the artist once, and then after that show all the albums for that artist, you'd do this:

    SELECT Title.TitleID, Title.Title, Title.GenreID, Genre.Genre, Genre.GenreID,
    artist.Artist, artist.ArtistID, Title.AlbumID, album.AlbumID, album.Album
    FROM Title, Genre, artist, album
    WHERE Title.GenreID=Genre.GenreID AND Title.ArtistID=artist.ArtistID
    AND Title.AlbumID=album.AlbumID
    ORDER BY Artist, Album

    And then output it like this:

    <cfoutput query="getAlbums" group="Artist">
    <b>Artist: #getAlbums.artist#</b><br>
    <cfoutput>
    Album: #getAlbums.album#<br>
    </cfoutput>
    <br>
    </cfoutput>
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    16
    Rep Power
    0
    This looks great, but when I try and use it, I get
    Element ALBUM is undefined in GETALBUMBS. What does this mean? I have a recordset called getalbums, which the SQL is listed above...
  14. #8
  15. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,265
    Rep Power
    968
    Well I just called the recordset "getAlbums". For you it would be whatever name you are giving your query in the CFQUERY tag.
  16. #9
  17. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    16
    Rep Power
    0
    Yeah I got that, so I named my query getalbums. Caps don't make a difference do they?
  18. #10
  19. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,265
    Rep Power
    968
    No, CF is not case sensitive.

IMN logo majestic logo threadwatch logo seochat tools logo