conditional join (I think) in SQL

[sheriberi]

I am trying to join a field (in table 1) to table 2 or table 3 depending on the value of a another field in table 1.

My COMM table (1) looks like this:
CommID User ID Type
100 987 SER
101 123 EMP

SER (2) and EMP (3) are tables that will give me information about those Users. I need to join the User ID from the COMM table to teh SER and EMP tables dependent on the Type field in COMMM. This is NOT working but may illustrate what I am trying to do:

select *
CASE WHEN comm.type = 'SER' THEN ser.userName ELSE emp.userName END
FROM COMM
LEFT JOIN SER on SER.userID = comm.UserID and comm.type = 'SER'
LEFT JOIN EMP on EMP.userID = comm.UserID and comm.type = 'EMP'

Any ideas?

 

[sheriberi]

Also.....(I do really have a comma after the * in the select stm). The syntax of this query is okay. I am getting results. I am just not getting correct results. I am getting correct results for the row that has EMP in the Type, but am getting NULL in the row that has SER as the Type when I can see that there should be a value.

 

[tran008]

Not sure about the condition join, but I would do a union

select field1, field2, field3
from OOMM
inner join SER
on comm.userid=ser.userid
where comm.type='SER'
union all
select field1, field2, field3
from OOMM
inner join EMP
on comm.userid=emp.userid
where comm.type='EMP'

 

[jjefferson]

I'm thinking I would leave the "and comm.type = 'xxx'" out of the JOIN. Even if you have userID's duplicated between SER and EMP, your CASE is going to get the name from the correct table.

 

[sheriberi]

I figured it out. My query is just fine, I am using a field that I shouldn't...AAARRRGGGHHH!!!!

Thanks though! Problem solved