Help with multi Join or multi tier select.

Hello,

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

PlanProviderProviderLocationLookups
--------------
PlanIDProviderIDProviderIDLookupType
PlanNamePlanIDProviderStatusLookupKey
RegionIDLastName...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.DisplayLocationOnPCP,
pl.NoDisplayDate, pl.ProviderStatus, 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 ProviderLocation 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.ProviderStatus = 0
ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNum

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

DavidTry moving the predicate "AND PL.providerstatus = 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 ProviderLocation AS PL
ON P.providerid = PL.providerid
AND PL.providerstatus = 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
--|||No that didn't work becase then it all the providers ... and I think
only the locations with with providerstatus = 0.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<RpOdnffKDN6GPVfdRVn-sQ@.giganews.com>...
> Try moving the predicate "AND PL.providerstatus = 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 ProviderLocation AS PL
> ON P.providerid = PL.providerid
> AND PL.providerstatus = 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'|||I understood that you wanted to include rows from Plans which didn't have
corresponding rows from ProviderLocation - 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
--|||>> I am trying to construct a query across 5 tables but primarily 3
tables. Plan, Provider, ProviderLocation 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 "LookupValue", 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.|||[posted and mailed, please reply in news]

David Logan (ibflyfishin@.yahoo.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.DisplayLocationOnPCP,
> pl.NoDisplayDate, pl.ProviderStatus, 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 ProviderLocation 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.ProviderStatus = 0
> ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNum
> 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.DisplayLocationOnPCP,
pl.NoDisplayDate, pl.ProviderStatus, 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 ProviderLocation pl ON p.ProviderID = pl.ProviderID
AND pl.ProviderStatus = 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.SiteLocationNum

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, esquel@.sommarskog.se

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