CODEWITHSUNDEEP
sql-server
user3749447
user3749447

Reputation: 299

SQL Server: Using multiple joins

I have 2 distinct joins that I'm not sure how to put together in 1 query. Both queries pull correctly in that it pulls info from PROVIDERS with the first joining PostalCodes's county to city. The 2nd query joins only providers with 'general acute care hospital' from table Taxonomy

What is the proper way to correctly use both these joins in 1 query? Please let me know if I am missing any crucial information with my quesetion.

query 1 to join PostalCodes.County

 SELECT     PROVIDERS.ProviderID, PROVIDERS.NAME, PROVIDERS.CITY, PROVIDERS.STATE, PROVIDERS.ZIP, PostalCodes.County                  
FROM  PostalCodes INNER JOIN
                    PROVIDERS ON PostalCodes.City = PROVIDERS.CITY
WHERE     (1=1)
AND (PROVIDERS.STATE = 'AL')

query 2 to join Taxonomy.[Taxonomy Specialization]

  SELECT     PROVIDERS.ProviderID, PROVIDERS.NAME, PROVIDERS.CITY, PROVIDERS.STATE, PROVIDERS.ZIP, NPI.[Provider First Line Business Practice Location Address] as "ADDRESS"                                      
FROM               
                    Taxonomy INNER JOIN
                      NPI_Taxonomy ON Taxonomy.[Taxonomy Code] = NPI_Taxonomy.[Healthcare Provider Taxonomy Code] 
                      RIGHT OUTER JOIN PROVIDERS ON NPI_Taxonomy.NPI = PROVIDERS.FACILITYID
                      right outer join npi on PROVIDERS.FACILITYID = NPI.NPI
WHERE     (1=1)
AND (PROVIDERS.STATE = 'AL')
AND ([Taxonomy Specialization] like 'general acute care hospital')

Upvotes: 0

Views: 67

Answers (1)

xQbert
xQbert

Reputation: 35343

WITH POSTALPROVIDERS AS 
(SELECT PROVIDERS.ProviderID, PROVIDERS.NAME, PROVIDERS.CITY, PROVIDERS.STATE, PROVIDERS.ZIP, PostalCodes.County                  
FROM  PostalCodes 
INNER JOIN PROVIDERS 
  ON PostalCodes.City = PROVIDERS.CITY
WHERE (1=1)
  AND (PROVIDERS.STATE = 'AL')),

TAXONOMY2 as 
( SELECT PROVIDERS.ProviderID, PROVIDERS.NAME, PROVIDERS.CITY, PROVIDERS.STATE, PROVIDERS.ZIP, NPI.[Provider First Line Business Practice Location Address] as "ADDRESS"                                      
FROM Taxonomy 
INNER JOIN NPI_Taxonomy ON Taxonomy.[Taxonomy Code] = NPI_Taxonomy.[Healthcare Provider Taxonomy Code] 
RIGHT OUTER JOIN PROVIDERS 
 ON NPI_Taxonomy.NPI = PROVIDERS.FACILITYID
RIGHT OUTER JOIN npi 
 on PROVIDERS.FACILITYID = NPI.NPI
WHERE 1=1)
AND (PROVIDERS.STATE = 'AL')
AND ([Taxonomy Specialization] like 'general acute care hospital'))

SELECT * 
FROM POSTALPROVIDERS PP
INNER JOIN TAXONOMY2 T
 ON PP.PROVIDERID = T.ProviderID

But if you must for performance reasons: this may do it.... setup a SQL Fiddle and I'll ensure they work.

SELECT p.ProviderID, P.NAME, P.CITY, P.STATE, P.ZIP, PC.County,  NPI.[Provider First Line Business Practice Location Address] as "ADDRESS"
FROM  PostalCodes PC
INNER JOIN PROVIDERS  P
  ON PC.City = P.CITY
LEFT JOIN NPI_Taxonomy NT
 on NT.NPI = P.FacilityID
LEFT JOIN Taxonomy T
  ON Taxonomy.[Taxonomy Code] = NPI_Taxonomy.[Healthcare Provider Taxonomy Code] 
 AND ([Taxonomy Specialization] like 'general acute care hospital'))
WHERE (1=1)
  AND (P.STATE = 'AL')

We have to move the limiting criteria on taxonomy to the join criteria otherwise we will lose providers and postal codes when the join to NPI_Taxonomy doesn't exist.

Assumption here is you want all providers and postal codes in AL and any related taxonomy information that exists but keep all the provider and postal codes if no taxonomy information is found.

This is done by joining proviers to postal codes as both queries indicated. then left joining in the NPI_taxonomy and taxonomy. We have to move the where clause of the taxonomy to the join or lose the provider information.

Upvotes: 1

Related Questions