Conditional Join

[TonyVance]

I have two tables, task and webuser. Task has a field called repid which is an int containing the unique id for a webuser record. Webuser has two fields firstname and lastname.

I need to run a select joining task and webuser so that I get the task record and the firstname and lastname of the webuser.

The problem is I use zero as a special code for repid. I need task records that have a repid of zero to only show up once in the list, and the content of firstname and lastname does not matter. The following SQL displays records, one each for ever web user.

SELECT repid, firstname, lastname FROM task, webuser WHERE
repid=webuser.id AND task.taskdate='5/5/2007'

Any help would be appreciated.

 

[PHV]

As you asked in the ANSI_SQL forum, here an ANSI SQL way:
SELECT DISTINCT T.repid, W.firstname, W.lastname
FROM task T LEFT JOIN webuser W ON T.repid = W.id AND T.taskdate = DATE '2007-05-05'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886