Cool, I was able to get it to return the 1 record by putting () around the p.TITLE section of the query,...wierd...will try your way it sounds safer...
Brad >|> -----Original Message----- >|> From: Jacob Cameron [mailto:[EMAIL PROTECTED] >|> Sent: Tuesday, August 05, 2003 5:20 PM >|> To: SQL >|> Subject: RE: Query Assistance Needed... >|> >|> >|> Making the assumption that the u table is the one you want the >|> users from >|> PK=ID and that relates to the FK=p.speakerID I am assuming, >|> this should give >|> you the desired results: >|> >|> SELECT >|> p.ID, p.CongressID >|> FROM >|> UsersPresenter u left join Presentation p on u.ID = p.SpeakerID AND >|> p.Deleted = 0 >|> WHERE >|> ( >|> >|> ( (u.FirstName = 'jan' >|> OR >|> u.FirstName LIKE '%jan%' >|> OR >|> u.LastName = 'jan' >|> OR >|> u.LastName LIKE '%jan%' >|> OR >|> u.MiddleName = 'jan' >|> OR >|> u.MiddleName LIKE '%jan%' >|> OR >|> (u.FirstName + ' ' + u.LastName) LIKE '%jan%' >|> OR >|> (u.FirstName + ' ' + u.LastName) = 'jan' >|> OR >|> (u.FirstName + ' ' + u.MiddleName + ' ' + >|> u.LastName) LIKE '%jan%') >|> ) >|> AND >|> >|> p.Title = 'plant' >|> OR >|> p.Title LIKE '%plant%' >|> AND >|> >|> 1 = 1) >|> ORDER BY >|> p.CongressID, >|> >|> p.Title >|> >|> -----Original Message----- >|> From: Bradford T Comer [mailto:[EMAIL PROTECTED] >|> Sent: Tuesday, August 05, 2003 5:11 PM >|> To: SQL >|> Subject: Query Assistance Needed... >|> >|> Good Day All, >|> >|> I have the following dynamic query, it seems to work, in a matter of >|> speaking; it returns the correct record but duplicates it 4 times in the >|> display. Can anyone tell me how to accomplish a 1 record >|> retrieval with the >|> following query? I can NOT use DISTINCT(p.ID); as the query is >|> dynamically >|> rendered it keeps telling me I have to have the columns in the >|> ORDER BY in >|> the DISTINCT query... >|> >|> Thanks In Advance! >|> Brad >|> [EMAIL PROTECTED] >|> >|> >|> /* QUERY */ >|> >|> SELECT >|> p.ID, p.CongressID >|> FROM >|> Presentation p , UsersPresenter u >|> >|> WHERE >|> ( >|> >|> ( (u.FirstName = 'jan' >|> OR >|> u.FirstName LIKE '%jan%' >|> OR >|> u.LastName = 'jan' >|> OR >|> u.LastName LIKE '%jan%' >|> OR >|> u.MiddleName = 'jan' >|> OR >|> u.MiddleName LIKE '%jan%' >|> OR >|> (u.FirstName + ' ' + u.LastName) LIKE '%jan%' >|> OR >|> (u.FirstName + ' ' + u.LastName) = 'jan' >|> OR >|> (u.FirstName + ' ' + u.MiddleName + ' ' + >|> u.LastName) LIKE '%jan%') >|> AND >|> p.SpeakerID = u.ID) >|> AND >|> >|> p.Title = 'plant' >|> OR >|> p.Title LIKE '%plant%' >|> AND >|> >|> 1 = 1 AND p.Deleted = 0 >|> ) >|> ORDER BY >|> p.CongressID, >|> >|> p.Title >|> >|> /* END QUERY */ >|> >|> >|> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6 Get the mailserver that powers this list at http://www.coolfusion.com
