Forum Discussion

JoeCavasin's avatar
JoeCavasin
Brass Contributor
Aug 14, 2025

Assistance with SQL Joins - or - using Nested/Subquery?

Relative noob to SSMS, however have had decent success in a few simple queries with 4-7 joins.  In two separate queries i can capture two populations of data that apply to and link through a group of provider ID's.  The two queries are as follows:

1. identify group of providers set to active status on or after a given approval date.

2. identify all plans and addresses linked to each provider 

Each of those function perfectly on their own, but i am struggling to pull the providers active approval date (Q1) (which is it's own column) into the output of Query 2, listing of all plans and addresses linked to that provider.  In these scenarios it's important to note - A provider will have a single approval date, multiple addresses, and each address will be tied to 15-17 insurance plans - so a single to many to many relationship.   Assignment_RTK is our systems key for 'Insurance Plan', Entity_K is key for which organization a provider is working for, and Provider_K is the provider key.  Provider_K, Entity_K, and OriginalAppointmentDate all live in the EA (Entity Assignments) table.  Plan keys in the form of Assignment_RTK live, somewhat confusingly in the EntityAssignment table as well, but adding either the provider approval date as a criteria to Query 2 will result in no returns (but also no errors), and adding the Plan Keys (Assignment_RTK) to the Approval date query will result in Null values in Assignment_RTK column.  Copy of the queries below, and i've disguised tax id numbers for my organization.

 

Query 1 - 

SELECT PR.LastName,PR.FirstName,PR.DisplayDegrees,PR.Npi,PR.Id,GR.GROUPNAME,EA.*
FROM 
Visual_Cactus.visualcactus.EntityAssignments as EA
Join Visual_Cactus.visualcactus.Providers as PR on EA.Provider_K = PR.Provider_K
Join Visual_Cactus.visualcactus.PROVIDERADDRESSES as PA on PR.Provider_K = PA.PROVIDER_K
Join Visual_Cactus.visualcactus.GROUPADDRESS as GA on PA.ADDRESS_K = GA.ADDRESS_K
Join Visual_Cactus.visualcactus.GROUPS as GR on GA.GROUP_K = GR.GROUP_K
WHERE
EA.OriginalAppointmentDate >= DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),1)
and EA.Entity_K like '%TCHN' 
and GA.TAXIDNUMBER in ('xxxxxxxxx','xxxxxxxxx','xxxxxxxxx','xxxxxxxxx','xxxxxxxxx','xxxxxxxxx','xxxxxxxxx','xxxxxxxxx','xxxxxxxxx','xxxxxxxxx',)
 
Query 2
SELECT EAAD.*,GA.GROUP_K,PR.LongName,AD.AddressLine1,AD.AddressLine2,AD.City,AD.State,AD.ZipCode,EA.Assignment_RTK
FROM Visual_Cactus.visualcactus.Addresses as AD
Join Visual_Cactus.visualcactus.PROVIDERADDRESSES as PA on AD.Address_K = PA.ADDRESS_K
Join Visual_Cactus.visualcactus.Providers as PR on PA.PROVIDER_K = PR.Provider_K
Join Visual_Cactus.visualcactus.EAADDRESSES as EAAD on PA.PROVIDERADDRESS_K = EAAD.PROVIDERADDRESS_K 
Join Visual_Cactus.visualcactus.EntityAssignments as EA on EAAD.EA_K = EA.Ea_K
Join Visual_Cactus.visualcactus.GROUPADDRESS as GA on AD.ADDRESS_K = GA.ADDRESS_K
WHERE
EAAD.ACTIVE = '1' and EA.Assignment_RTK in ('D2GT0MDZ4G', 'D2EK0KVTUN', 'D2ER0FKD24', 'D2ER0FJWWG', 'D2ER0FGOP7', 'D2ER0FG8HL','C4I80SMAER','P36Y0ZCIX4','D2ER0FFJ74','D2ER0FE95G','D2ER0FDEUF','D2ER0F9KQ0','D2ER0FA7YH','C4V60N6TTC','D2ER0F7FBI','D2ER0F6PN4','D2OE0KNTKW') 

 

 

Resources