I have three tables:
I am trying to search for an event with a specific eventid and return the names of the cotrainers, but sometimes there is only one cotrainer and cotrainer2 contains a null value. this is what i have so far....
this is returning way to many records...i am at loss, i have been looking at it too long. anybody have an idea??
jgroove
Code:
tnevent tnagency tnuser
------- -------- ------
eventid agencyid userid
agencyid name firstname
cotrainer1id lastname
cotrainer2id
Code:
select a.*,
b.*,
c.firstname,c.lastname,
d.firstname,d.lastname
from tnevent a,tnagency b, tnuser c, tnuser d
where a.eventid='#url.event#' and
a.agencyid=b.agencyid and
((a.cotrainer1id=c.userid and a.cotrainer1id is not null) or
(a.cotrainer2id=d.userid and a.cotrainer2id is not null))
this is returning way to many records...i am at loss, i have been looking at it too long. anybody have an idea??
jgroove