First thing I saw was that your join is on "album", not "albums".
INNER JOIN titles // Joining the titles table
ON artists.id = titles.artist_id
AND album.id = titles.album_id
|---------------------------------+---------------------------------------|
|Eric A. Laney |If your bread is stale, make toast. |
|Systems Architect | |
|Verizon Security | |
|Voice: 813.987.1416 | Today's Fortune|
|Pager: 888.551.3718 | |
|---------------------------------+---------------------------------------|
"Douglas Brown"
<[EMAIL PROTECTED] To: SQL <[EMAIL PROTECTED]>
com> cc:
Subject: Query problem
04/19/2002 03:42
PM
Please respond to
sql
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
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
