How to use two select elements as if they were two scalars returned from subquery
The below doesn't work as a subquery because I'm trying to use the two select elements as if they were two elements in a set returned to the outer query. The values for Ref_for_Care_Coord_ID1 and Ref_for_Care_Coord_ID2 are both int as is the ct.contactid and there are rows to make the matching occur, I'm just wondering what technique I can use to make this work.
TIA
TIA
select ct.firstname, ct.lastname, ct.workphone, ct.Email
from contacts ct
where ct.contactid in (select
Ref_for_Care_Coord_1ID,
Ref_for_Care_Coord_2ID
from collaboratives
where collabrecid = 30)
go
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the fast solution!
You're welcome! Thanks for the points! Have a good week!
select ct.firstname, ct.lastname, ct.workphone, ct.Email
from contacts ct
LEFT JOIN collaboratives c1 On ct.contactid = c1.Ref_for_Care_Coord_1ID And c1.collabrecid = 30
LEFT JOIN collaboratives c2 On ct.contactid = c2.Ref_for_Care_Coord_2ID ANd c2.collabrecid = 30