Help with multi Join or multi tier select.

[David Logan]

Hello,

I am trying to construct a query across 5 tables but primarily 3
tables. Plan, Provider, ProviderLocatio n are the three primary tables
the other tables are lookup tables for values the other tables.
PlanID is the primary in Plan and

Plan Provider ProviderLocatio n Lookups
-------- ---------- ---------------- -----------
PlanID ProviderID ProviderID LookupType
PlanName PlanID ProviderStatus LookupKey
RegionID LastName ... LookupValue
.... FirstName ...

Given a PlanID I want all the Providers with a ProviderStatus = 0

I can get the query to work just fine if there are records but what I
want is if there are no records then I at least want one record with
the Plan information. Here is a sample of the Query:

SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,
p.ProviderID, p.SSNEIN, pl.DisplayLocat ionOnPCP,
pl.NoDisplayDat e, pl.ProviderStat us, pl.InvalidDate,
l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,
pl.InvalidData
FROM Plans pln
INNER JOIN Lookups l3 ON l3.LookupType = 'REGN'
AND pln.RegionID = l3.Lookupkey
left outer JOIN Provider p ON pln.PlanID = p.PlanID
left outer JOIN ProviderLocatio n pl ON p.ProviderID = pl.ProviderID
left outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'
AND pl.ReasonMain = l1.LookupKey
left outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'
AND pl.ReasonSub = l2.Lookupkey
WHERE pln.PlanID = '123456789' AND pl.ProviderStat us = 0
ORDER BY p.PlanID, p.ProviderID, pl.SiteLocation Num

I know the problew the ProviderStatus on the Where clause is keeping
any records from being returned but I'm not good enough at this to
another select.

Can anybody give me some suggestions?

Thanks

David

[David Portas]

Try moving the predicate "AND PL.providerstat us = 0" into the ON clause:

FROM Plans AS PLN
INNER JOIN Lookups L3
ON L3.LookupType = 'REGN'
AND PLN.regionid = L3.lookupkey
LEFT OUTER JOIN Provider AS P
ON PLN.planid = P.planid
LEFT OUTER JOIN ProviderLocatio n AS PL
ON P.providerid = PL.providerid
AND PL.providerstat us = 0
LEFT OUTER JOIN Lookups AS L1
ON L1.lookuptype = 'PLRM'
AND PL.reasonmain = L1.lookupkey
LEFT OUTER JOIN Lookups AS L2
ON L2.lookuptype = 'PLX1'
AND PL.reasonsub = L2.lookupkey
WHERE PLN.planid = '123456789'

--
David Portas
SQL Server MVP
--

[David Logan]

No that didn't work becase then it all the providers ... and I think
only the locations with with providerstatus = 0.

"David Portas" <RE************ *************** *@acm.org> wrote in message news:<Rp******* *************@g iganews.com>...

Try moving the predicate "AND PL.providerstat us = 0" into the ON clause:

FROM Plans AS PLN
INNER JOIN Lookups L3
ON L3.LookupType = 'REGN'
AND PLN.regionid = L3.lookupkey
LEFT OUTER JOIN Provider AS P
ON PLN.planid = P.planid
LEFT OUTER JOIN ProviderLocatio n AS PL
ON P.providerid = PL.providerid
AND PL.providerstat us = 0
LEFT OUTER JOIN Lookups AS L1
ON L1.lookuptype = 'PLRM'
AND PL.reasonmain = L1.lookupkey
LEFT OUTER JOIN Lookups AS L2
ON L2.lookuptype = 'PLX1'
AND PL.reasonsub = L2.lookupkey
WHERE PLN.planid = '123456789'

[David Portas]

I understood that you wanted to include rows from Plans which didn't have
corresponding rows from ProviderLocatio n - in which case they won't have a
ProviderStatus. It may be easier to understand your requirements if you post
DDL, sample data INSERTs and show your required result based on that sample
data. (http://www.aspfaq.com/5006)

--
David Portas
SQL Server MVP
--

[--CELKO--]

>> I am trying to construct a query across 5 tables but primarily 3
tables. Plan, Provider, ProviderLocatio n are the three primary tables
the other tables are lookup tables for values the other tables. <<

Mind posting some DDL? When see data element names as poorily written
as "LookupType ", "LookupKey" and "LookupValu e", it is a pretty sure
sign that the basic schema design is wrong. To be is to be something
in particular and those names imply that you have a "One True Lookup
Table" (OTLT) flaw. Yes, like many diseases or disasters, it is
common enough to have a name! Google it; I wrote a column on it in
INTELLIGENT ENTERPRISE magazine.

[Erland Sommarskog]

[posted and mailed, please reply in news]

David Logan (ib*********@ya hoo.com) writes:

I can get the query to work just fine if there are records but what I
want is if there are no records then I at least want one record with
the Plan information. Here is a sample of the Query:

SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,
p.ProviderID, p.SSNEIN, pl.DisplayLocat ionOnPCP,
pl.NoDisplayDat e, pl.ProviderStat us, pl.InvalidDate,
l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,
pl.InvalidData
FROM Plans pln
INNER JOIN Lookups l3 ON l3.LookupType = 'REGN'
AND pln.RegionID = l3.Lookupkey
left outer JOIN Provider p ON pln.PlanID = p.PlanID
left outer JOIN ProviderLocatio n pl ON p.ProviderID = pl.ProviderID
left outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'
AND pl.ReasonMain = l1.LookupKey
left outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'
AND pl.ReasonSub = l2.Lookupkey
WHERE pln.PlanID = '123456789' AND pl.ProviderStat us = 0
ORDER BY p.PlanID, p.ProviderID, pl.SiteLocation Num

I know the problew the ProviderStatus on the Where clause is keeping
any records from being returned but I'm not good enough at this to
another select.

As David said, it is always a good idea to include CREATE TABLE and
sample data. But I think I have a guess what will work for you:

SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,
p.ProviderID, p.SSNEIN, pl.DisplayLocat ionOnPCP,
pl.NoDisplayDat e, pl.ProviderStat us, pl.InvalidDate,
l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,
pl.InvalidData
FROM Plans pln
JOIN Lookups l3 ON l3.LookupType = 'REGN'
AND pln.RegionID = l3.Lookupkey
LEFT JOIN (Provider p
JOIN ProviderLocatio n pl ON p.ProviderID = pl.ProviderID
AND pl.ProviderStat us = 0
JOIN Lookups l1 ON l1.LookupType = 'PLRM'
AND pl.ReasonMain = l1.LookupKey
JOIN Lookups l2 ON l2.LookupType = 'PLX1'
AND pl.ReasonSub = l2.Lookupkey)
ON pln.PlanID = p.PlanID
WHERE pln.PlanID = '123456789'
ORDER BY p.PlanID, p.ProviderID, pl.SiteLocation Num

The point here is that the thing in parathensis is sort of a logical
table, and you make an outer-join to that logical table.

This is the normal way of doing things when you want to join a
left-joined table with a lookup table (should not be necessary to
left-join the lookup table). In this case it also necessary, to
exclude providers which does not have any location with status = 0.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp