SQL Server Performance Forum – Threads Archive

Joins with OR

I have a table of all currently active plans, dbo.planmast, and two other tables with participant and dependent plans. The tables with participant and dependent plans each have 16 plan columns. If a plan exists in dbo.planmast, it does not have to exist in both the participant and dependent tables but it may exist in one. I am trying to create a query that will tell me all of the plans in dbo.planmast that do not have any matches in dbo.person_coverage or dbo.dependent_coverage. This is what I have so far.
Thanks. SELECT pl.plan
FROM dbo.planmast pl
INNER JOIN dbo.person_coverage pe ON (pl.plan = pe.peplan1
OR pl.plan = pe.plan2
OR pl.plan = pe.plan3
OR pl.plan = pe.plan4
OR pl.plan = pe.plan5
OR pl.plan = pe.plan6
OR pl.plan = pe.plan7
OR pl.plan = pe.plan8
OR pl.plan = pe.plan9
OR pl.plan = pe.plan10
OR pl.plan = pe.plan11
OR pl.plan = pe.plan12
OR pl.plan = pe.plan13
OR pl.plan = pe.plan14
OR pl.plan = pe.plan15)
LEFT OUTER JOIN dbo.dependent_coverage dpe ON (pl.plan = dpe.plan1
OR pl.plan = dpe.plan2
OR pl.plan = dpe.plan3
OR pl.plan = dpe.plan4
OR pl.plan = dpe.plan5
OR pl.plan = dpe.plan6
OR pl.plan = dpe.plan7
OR pl.plan = dpe.plan8
OR pl.plan = dpe.plan9
OR pl.plan = dpe.plan10
OR pl.plan = dpe.plan11
OR pl.plan = dpe.plan12
OR pl.plan = dpe.plan13
OR pl.plan = dpe.plan14
OR pl.plan = dpe.plan15)

---

I think you need to use left outer join on both cases and check the matching id not in person_coverage and dependent_coverage tables so that you get all the records that exist in planmast but not in other two tables. This is the basic logic:
select m.id
from planmast m
left outer join person_coverage p
on m.id = p.id
left outer join dependent_coverage d
on d.id = p.id
where p.id IS NULL
AND d.id IS NULL
Check using LEFT OUTER JOIN and Subquery in BOL. Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

]]>