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
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
