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?

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.

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

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.

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.

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>

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...

Well I just called the recordset "getAlbums". For you it would be whatever name you are giving your query in the CFQUERY tag.

Yeah I got that, so I named my query getalbums. Caps don't make a difference do they?

No, CF is not case sensitive.