473,853 Members | 1,957 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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, 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
Jul 20 '05 #1
5 3270
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
--
Jul 20 '05 #2
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'

Jul 20 '05 #3
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
--
Jul 20 '05 #4
>> 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.
Jul 20 '05 #5
[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
Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1534
by: Pete | last post by:
hello i am a novice at database design and i'm having trouble doing a multi join in mysql, i was able to do a inner join with just the venues and productions tables below like so SELECT productions.PID, productions.EID,productions.OID, venues.Name, venues.VID, venues.City, venues.State, productions.P_Date FROM productions INNER JOIN venues ON productions.VID = venues.VID where productions.VID=50
1
3433
by: Stephen Patten | last post by:
Hi All, While in the process of building my table (40 or so Insert statments) can I then query ("select * from @Table_variable") and use the results up to theat point for another insert into @Table_varible? If you look for stepID -15 I have commented that section out due to it not retuning the correct values. Thank you in advance
0
3030
by: Gareth Stretch | last post by:
Hi Guys. i am using C#.net connecting to an Access database using OleDbConnection i am using the following select Statement to join 3 tables string strdvds = "SELECT dvd.name, category.description, dvd.zone, dvd.price, dvd.booked_out, dvd.booked_out_date,(contacts.name +' '+ contacts.surname) as contact FROM (dvd LEFT JOIN category ON dvd.category_id = category.id) LEFT JOIN contacts ON dvd.contact_id = contacts.id ORDER BY dvd.name...
7
4591
by: Jon Davis | last post by:
I have a couple questions. First of all, would anyone consider a multi-layered programming approach (building business objects that are seperate from data access logic and seperate from user interface logic but that interface the UI and the data) an n-tier implementation by definition? Or does n-tier necessitate a network-distributed architecture (beyond IIS and SQL Server, i.e. COM+/MTS/MSMQ/WCF) in addition to the layered programming? If...
4
2277
by: Dia | last post by:
Hi there, I struggle to get this going i would like to insert data into 2 tmp tables in a view. If i run the code on it's own it works perfectly until i want to create a view it complains about the INSERT this is my code
9
3952
by: pic078 via AccessMonster.com | last post by:
I need serious help - I have a frontend/backend Access database (2 MDE Files) that remains stuck in task manager after exiting the application - you can't reopen database after exiting as a result - I have read every post out there and spent hours trying to figure out the problem with no success whatsoever - I have constrained the problem to one form however, and I think it's hiding somewhere in my code associated with this form, which is...
31
2810
by: zdenko | last post by:
I have a multi user database and users were created by user level security wizzard - as I mentioned in message before. Everything works fine for those users, but now I have another problem. I have another database with linked tables from secured database. How can I approach secured database from the unsecure one? Thx
0
1288
by: stanlew | last post by:
Happy New Year everyone! I'm new to both T-SQL and this forum. I'm currently doing an internship and my first task was to create a small program which will send an email detailing the sales of the previous day versus monthly targets and sales. Most of the parts were figured out and eveything was done in Visual Studio. The gist of the code was written in one large chunk of SQL code, as below: SELECT derivedtbl_1.family AS 'Family',...
0
1308
victorduwon
by: victorduwon | last post by:
Hi Folks, i have a list based navigation menu with two tiers of navigation. The second tier displays when the tabs on the first tier are hovered over. The problem I have is that when the tabs are clicked on the second tier dissapears. I need the second tier for the active tab to remain visible and the background of the tab to remain visible when the tab is clicked/active. Someone please help me, this is driving me crazy. Below is the CSS. ...
0
9898
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9748
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10670
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10735
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10358
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9506
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7074
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4549
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3180
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.