I have the following query being used to allow customers to search for artists by name
or group, but I want to add in the ability to
search by song title also. Problem is that when I add in the titles to the query with
a join, I get multiple results of the same
album. Can someone help me here, I am too tired to figure this out or am just brain
dead.
TIA
<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 // Here is where I am adding the titles to the query
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 // Joining the titles table
ON artists.id = titles.artist_id
AND album.id = titles.album_id
WHERE right(products.sku,2) = 'CD'
AND
<CFIF isDefined("searchOptions")>
<CFIF searchOptions EQ "1">
(artistFirstName LIKE '%#searchString#%')
OR (artistLastName LIKE '%#searchString#%')
OR (artistFirstName + ' ' + artistLastName LIKE '%#searchString#%')
OR (groupName LIKE '%#searchString#%')
<CFELSEIF searchOptions EQ "2">
musicTypes.musicType = '#type#'
<CFELSEIF searchOptions EQ "3">
album_name LIKE '%#searchString#%'
<CFELSEIF searchOptions EQ "4">
songTitle LIKE '%#searchString#%'
</CFIF>
</CFIF>
ORDER BY artists.id
</CFQUERY>
Doug Brown
"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
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
