Forum Discussion
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 -
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
1 Reply
- rodgerkongIron Contributor
Could you please provide the structure of tables and some sample data?