It sounds like your problem is not in the query, but in the output of the
query. Of course you will pull multiple entries with the same album, that
is to be expected. Try outputting like this:
<cfoutput query="music" group="musicType">
#musicType#<br>
<cfoutput group="albumTitle">
-- #albumTitle#<br>
<cfoutput>
---- #songTitle#<br>
</cfoutput>
</cfoutput>
</cfoutput>
but in order for this to work you MUST AT LEAST order by musicType and
albumTitle:
ORDER BY musicType, albumTitle [add more here if you want]
+-----------------------------------------------+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
Telecommunication Systems
[EMAIL PROTECTED]
+-----------------------------------------------+
"...'If there must be trouble, let it be in my day, that my child may have
peace'..."
- Thomas Paine, The American Crisis
-----Original Message-----
From: Douglas Brown [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 22, 2002 12:20 PM
To: CF-Talk
Subject: OT: SQL select question
I know that my problem lies in a one to many relationship, but I cannot seem
to figure this out without jerry rigging the query. I
want customers to be able to do a search by artist, album, music type and
song title. Each album has several songTitles and
therefore my problem arises. I am using the same query for all the search
options using an IF statement to determine the search
paramaters. Whenever someone searches for music by (music type) it pulls
multiple of the same album due to the join of the titles
table.
Table layouts
[artists]
ID (PK)
artist_name
[albums]
ID (PK)
artist_id (FK_artists)
album_name
[titles]
ID (PK)
artist_id (FK_artists)
album_id (FK_albums)
songTitle
Here is the query currently. I know I should be able to mold it into just
one query, and this is just how I have rigged it to work.
This irritates me though and would like to see if it can be fixed.
<cfif isDefined("searchOptions")>
<!--- If the search value is anything other than by songTitle, use this
query without the title table join//--->
<cfif (searchOptions EQ 1) OR (searchOptions EQ 2) OR (searchOptions EQ
3)>
<CFQUERY DATASOURCE="cmg" NAME="get_products_search">
SELECT distinct(artists.id) AS artist_id,
artists.artistfirstName,
artists.artistLastName,
artists.groupName,
artists.musicType_ID,
albums.id AS album_id,
albums.album_name,
albums.releaseDate,
albums.smImage,
albums.lgImage,
products.id AS products_id,
products.sku,
products.salePrice,
musicTypes.musicType
FROM artists
INNER JOIN albums
ON artists.ID = albums.artist_id
INNER JOIN products
ON albums.id = products.album_id
INNER JOIN musicTypes
ON artists.musicType_ID = musicTypes.ID
WHERE right(products.sku,2) = 'CD'
AND
<CFIF searchOptions EQ "1">
(artistFirstName = '#searchString#')
OR (artistLastName = '#searchString#')
OR (artistFirstName + ' ' + artistLastName = '#searchString#')
OR (groupName = '#searchString#')
<CFELSEIF searchOptions EQ "2">
musicTypes.musicType = '#type#'
<CFELSEIF searchOptions EQ "3">
album_name = '#searchString#'
</CFIF>
</CFQUERY>
<cfelse>
<!---If the searchoption is by songTitle insert the join into the query for
the titles table//--->
<CFQUERY DATASOURCE="cmg" NAME="get_products_search">
SELECT distinct(artists.id) AS artist_id,
artists.artistfirstName,
artists.artistLastName,
artists.groupName,
artists.musicType_ID,
albums.id AS album_id,
albums.album_name,
albums.releaseDate,
albums.smImage,
albums.lgImage,
products.id AS products_id,
products.sku,
products.salePrice,
musicTypes.musicType,
titles.songTitle
FROM artists
INNER JOIN albums
ON artists.ID = albums.artist_id
INNER JOIN products
ON albums.id = products.album_id
INNER JOIN musicTypes
ON artists.musicType_ID = musicTypes.ID
INNER JOIN titles
ON artists.id = titles.artist_id
AND albums.id = titles.album_id
WHERE right(products.sku,2) = 'CD'
AND songTitle = '#searchString#'
</CFQUERY>
</cfif>
</cfif>
"Success is a journey, not a destination!!"
Doug Brown
______________________________________________________________________
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
