left join to get info from a table with foreign keys

Hello,

I am still new to SQL and I am having trouble obtaining the results I need from a query.   I have worked on this command for some time and searched the internet but cannot seem to still get it correct.

I have a table called Patient.    It's primary key is pat_id.

I have a second table called Coverage.   It has no primary key.   The foreign keys are pat_id, coverage_plan_id, and hosp_status.

I have a third table called Coverage_History.   It has a primary key consisting of pat_id, hosp_stat, copay_priority, and effective_from. 

I want to get the pat_id and all the coverage information that is current.   The coverage table contains specific insurance policy information.   The coverage_history table will indicate the effective dates for the coverage.   So the tables could contain something like this:

Patient

P123

P124

P125

Coverage

P123    MED1

September 13th, 2015 9:37am

Your question is too vague to warrant an attempt to answer. Generally, for this type of questions, it is a good idea to post:

1) CREATE TABLE statements for your tables.
2) INSERT statements with sample data, enough to illustrate the problem.
3) The desired result given the sample.
4) A short description of the business problem you are trying to solve.
5) Which version of SQL Server you are using.

This makes it easy to copy and paste into a query window to develop a tested solution.

Free Windows Admin Tool Kit Click here and download it now
September 13th, 2015 10:04am

I am still new to SQL and I am having trouble obtaining the results I need from a query.   I have worked on this command for some time and searched the internet but cannot seem to still get it correct.

I have a table called Patient.    It's primary key is pat_id.

I have a second table called Coverage.   It has no primary key.   The foreign keys are pat_id, coverage_plan_id, and hosp_status.

I have a third table called Coverage_History.   It has a primary key consisting of pat_id, hosp_status, copay_priority, and effective_from. 

I want to get the pat_id and all the coverage information that is current.   The coverage table contains specific insurance policy information.   The coverage_history table will indicate the effective dates for the coverage.   So the tables could contain something like this:

Patient (pat_id and lname)
P123  Monto
P124  Minto
P125  Dento
P126  Donto

Coverage (pat_id, coverage_plan_id, hosp_status, policy_num)

P123     MED1   OP   A1499
P123     ACT4   OP   H39B 
P124     MED1   OP   C90009
P124     RAC    OP   99KKKK
P124     RAC    OP   99KKKK
P124     MED1   OP   C90009
P125     ARP    OP   G190
P126     BCB    OP   H88

Coverage_History (pat_id, hosp_status, copay_priority, effective_from, coverage_plan_id, effective_to)

P123   OP   1   20150102  MED1    NULL
P123   OP   2   20150102  ACT4    NULL
P124   OP   1   20150203  RAC     20150430
P124   OP   2   20150203  MED1    20150430
P124   OP   1   20150501  MED1    NULL
P124   OP   2   20150501  RAC     NULL
P125   OP   1   20150801  ARP     NULL 
P126   OP   1   20150801  BCB     20160101

select p.pat_id, p.lname, ch.coverage_plan_id, ch.hosp_status, ch.effective_from, ch.effective_to, ch.copay_priority,
       from patient p
  
     left join
  ( coverage_history ch left join coverage c on ch.coverage_plan_id = c.coverage_plan_id and ch.patient_id = c.patient_id and
                                   (ch.effective_to is NULL or ch.effective_to >= getdate() )
         ) on ch.patient_id = p.patient_id

        
    where ( ch.effective_to is NULL or ch.effective_to >= getdate() )
   
So I want to see:


P123  Monto  MED1  OP   20150102    NULL       1
P123  Monto  ACT4  OP   20150102    NULL       2
P124  Minto  MED1  OP   20150501    NULL       1
P124  Minto  RAC   OP   20150501    NULL       2
P125  Dento  ARP   OP   20150801    NULL       1
P126  Donto  BCB   OP   20150801    20160101   1

September 13th, 2015 10:11am

I think you want something like this :

SELECT *
FROM Coverage_history ch
LEFT JOIN Coverage c on ch.pat_id=c.pat_id
JOIN Patient p ON p.pat_id  =cd.pat_id
of course you have to replace * with the columns you want.. 
Free Windows Admin Tool Kit Click here and download it now
September 13th, 2015 11:06am

SELECT p.pat_id, p.lname, ch.coverage_plan_id, ch.hosp_status,       
       ch.effective_from, ch.effective_to, ch.copay_priority
FROM   patient p
JOIN   coverage_history ch ON p.pat_id = ch.pat_id
WHERE  ch.effective_from <= convert(date, sysdatetime())
   AND isnull(ch.effective_to, '99991231') >= convert(date, sysdatetime())

September 13th, 2015 12:38pm

>> I am still new to SQL and I am having trouble obtaining the results I need from a query. <<

I am not new to SQL and I still have the same problem :) But why did you fail to follow forum rules? Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data (https:', '/xkcd.com/1179/). We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL. 

And you need to read and download the PDF for: https:--www.simple-talk.com-books-sql-books-119-sql-code-smells-

>> I have worked on this command [sic] for some time and searched the internet but cannot seem to still get it correct. <<

SQL has statements, not commands. This is a declarative language. You are missing a basic concept. 

>> I have a table called Patients. It's PRIMARY KEY is pat_id. <<

You got that wrong without any DDL. A table models a set; therefore the name has to be plural or (better) collective. The table could be name Patients or perhaps census if this is a hospital and your want to use medical terms. 

>> I have a second table called Coverage. It has no PRIMARY KEY. The FKs are pat_id, coverage_plan_id, and hosp_status. <<

NO! NO! By definition, a table has a key. You can have a FK pat_id that references Patients and a FK  coverage_plan_id that references Coverage. But a status is not an entity! It is an attribute that tells us a state of being of an entity at a particular time. 

>> I have a third table called Coverage_History. It has a PRIMARY KEY consisting of pat_id, hosp_status, copay_priority, and effective_start_date. <<

This design flaw is called attribute splitting. Why do you think that the current coverage is a totally different from prior coverage? As unlike as squids and automobiles? The answer is that is what you would have done with paper forms in the 1950's; so you mimic it in SQL. Your third table should not exist in a valid schema. 

Here is a skeleton to record a history that has no gaps in its timeline:

CREATE TABLE Events
(event_id CHAR(10) NOT NULL,
previous_event_end_date DATE NOT NULL
CONSTRAINT Chained_Dates
REFERENCES Events (event_end_date),
event_start_date DATE NOT NULL,
event_end_date DATE UNIQUE, -- null means event in progress
PRIMARY KEY (event_id, event_start_date),
CONSTRAINT Event_Order_Valid
CHECK (event_start_date <= event_end_date),
CONSTRAINT Chained_Dates 
CHECK (DATEADD(DAY, 1, previous_event_end_date) = event_start_date).
<< other stuff for this event >>
);

-- disable the Chained_Dates constraint
ALTER TABLE Events NOCHECK CONSTRAINT Chained_Dates

-- insert a starter row
INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
VALUES ('Foo Fest', '2010-01-01', '2010-01-02', '2010-01-05');

-- enable the constraint in the table
ALTER TABLE Events CHECK CONSTRAINT Chained_Dates

-- this works
INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
VALUES ('Glob Week', '2010-01-05', '2010-01-06', '2010-01-10');

-- this fails
INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
VALUES ('Snoob', '2010-01-09', '2010-01-11', '2010-01-15'); 

>> I want to get the pat_id and all the coverage information that is current. The coverage table contains specific insurance policy information. The coverage_history table will indicate the effective dates for the coverage. So the tables could contain something like this: <,

NO! Did you look at your Coverage (non-)table? It has two identical rows! 


INSERT INTO Coverage 
(pat_id, coverage_plan_id, hosp_status, policy_num)
VALUES
..
('P124','RAC','OP','99KKKK'),
('P124','RAC','OP','99KKKK'), -- NO! 
..;

The outer joins say that you expect to have patients without any coverage in the schema. Is that possible? How doyou get these orphans? Would they not be covered by charity\ public fund\ something? 

My guess, without any DDL or vlaid data is that the query might look like this whenteh schema is done right: 

SELECT P.pat_id, P.lname, CH.coverage_plan_id, CH.hosp_status, CH.effective_start_date, CH.effective_end_date, CH.copay_priority,
 FROM Patients AS P,
      Coverage_History AS CH 
WHERE CH.coverage_plan_id = P.coverage_plan_id 
 AND CH.Patient_id = P.Patient_id 
 AND CH.effective_end_date 
     >= COALESCE (CH.effective_end_date, CURRENT_TIMESTAMP));
Free Windows Admin Tool Kit Click here and download it now
September 13th, 2015 2:56pm

Hi MistyG,

I see "duplicates" in table coverage, if they are not and the sequence related to the copay_priority in table Coverage_History, please see the below sample.
CREATE TABLE Patient (pat_id VARCHAR(20), lname VARCHAR(20))
INSERT INTO Patient VALUES
('P123','Monto'),
('P124','Minto'),
('P125','Dento'),
('P126','Donto');

CREATE TABLE Coverage (pat_id VARCHAR(20), coverage_plan_id VARCHAR(20), hosp_status VARCHAR(20), policy_num VARCHAR(20))
INSERT INTO Coverage VALUES
('P123','MED1','OP','A1499'),
('P123','ACT4','OP','H39B'), 
('P124','MED1','OP','C90009'),
('P124','RAC',' OP','99KKKK'),
('P124','RAC',' OP','99KKKK'),
('P124','MED1','OP','C90009'),
('P125','ARP',' OP','G190'),
('P126','BCB',' OP','H88');

CREATE TABLE Coverage_History (pat_id VARCHAR(20), hosp_status VARCHAR(20), copay_priority INT, effective_from DATE, coverage_plan_id VARCHAR(20), effective_to DATE)
INSERT INTO Coverage_History VALUES
('P123','OP',1,'20150102','MED1',NULL),
('P123','OP',2,'20150102','ACT4',NULL),
('P124','OP',1,'20150203','RAC','20150430'),
('P124','OP',2,'20150203','MED1','20150430'),
('P124','OP',1,'20150501','MED1',NULL),
('P124','OP',2,'20150501','RAC',NULL),
('P125','OP',1,'20150801','ARP',NULL ),
('P126','OP',1,'20150801','BCB','20160101');
 
;WITH Cte AS
(
SELECT * ,
ROW_NUMBER() OVER(PARTITION BY pat_id,coverage_plan_id,hosp_status ORDER BY (SELECT 1))  AS RN
FROM Coverage
)
select p.pat_id, p.lname, ch.coverage_plan_id, ch.hosp_status, ch.effective_from, ch.effective_to, ch.copay_priority
       from patient p
	   left join
	    ( coverage_history ch left join Cte c on ch.coverage_plan_id = c.coverage_plan_id 
				and ch.pat_id = c.pat_id and ch.copay_priority=c.RN
                      ) on ch.pat_id = p.pat_id
where ( ch.effective_to is NULL or ch.effective_to >= getdate() )



If you have any question, feel free to let me know.
September 13th, 2015 10:23pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics