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
______________________________________________________________________
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.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
