Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

help with joining tables where nulls might exist

Status
Not open for further replies.

jgroove

Programmer
Jul 9, 2001
43
US
I have three tables:
Code:
tnevent          tnagency           tnuser
-------          --------           ------
eventid          agencyid           userid
agencyid         name               firstname
cotrainer1id                        lastname
cotrainer2id
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....
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
 
declare cursor abc1
for
select
a.cotrainerid1,a.cotrainerid2
from

a

open cursor abc1
fetch next from abc1 into @cotrn1,@cotrn2

while @@fetch_status =0
begin

select @firstname1 =firstname from c
where
userid =@cotrn1
select @firstname1 =firstname from c
where
userid =@cotrn2

select a.*,b.*,@firstname1,@firstname2

from a,b
where
a.agencyid =b.agencyid
and a.eventid =eventid

fetch next from abc1 into @cotrn1,cotrn2

end

close cursor abc1
deallocate cursor abc1
 
Try this.

SELECT a.*,
b.*,
c.firstname,c.lastname,
d.firstname,d.lastname
FROM (tnevent a
INNER JOIN tnagency b
ON a.agencyid=b.agencyid
INNER JOIN tnuser c
ON a.cotrainer1id=c.userid)
LEFT JOIN tnuser d
ON a.cotrainer1id=d.userid
WHERE a.eventid='#url.event#' Terry L. Broadbent
Programming and Computing Resources
 
Give this a shot:
select a.*,
b.*,
c.firstname,c.lastname,
d.firstname,d.lastname
from tnevent a,tnagency b
left join tnuser c on a.cotrainer1id=c.userid
left join tnuser d on a.cotrainer2id=d.userid
where a.eventid='#url.event#' and
a.agencyid=b.agencyid;

If c or d returns a null it should fetch the row


 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top