Create a view by joining three tables

Question

I am attempting to join three tables. One table has profile information, the second table has admission information and the third has discharge information.

Table 1: Member Information

No, 
Name, 
Sex, 
DBO, 
CaseNo, 
SeqNum,
StartDate,
......

Table 2: Auth Information

No,
Name,    
DBO,
CaseNo,
SeqNum,
StartDate,
Admin_1,
Admin_2,
Admin_3,
...

Table 3: Discharge Information

CaseNo,
SeqNum,
DisDate,
DisRea,
...

This is my query:

Select    a.no, 
          a.Name, 
          a.Sex, 
          a.DBO, 
          a.CaseNo, 
          a.SeqNum,
          a.StartDate,
          b.Admin_1,
          b.Admin_2,
          b.Admin_3,
          c.DisDate,
          c.DisRea
 from     dbo.mem_information as a inner join dbo.auth_information as b on b.caseno = a.caseno AND 
          b.seqnum = a.seqnum AND        
          b.StartDate = a.StartDate 
          inner join dbo.discharge_information as c ON c.caseno = b.caseno AND
          c.seqnum = b.seqnum

Sample of my results:

 Name   Sex     DBO     CaseNo  SeqNum  Admin_1  Admin_2 Admin_3 DisDate DisRea
  Jones   M    19980615    23      1       SING
  Jones   M    19980615    23      1               LAUGH
  Smith   F    19960212    24      1       SING

Desired Results:

 Name  Sex     DBO     CaseNo  SeqNum  Admin_1  Admin_2 Admin_3 DisDate DisRea
 Jones   M    19980615    23      1     SING     LAUGH            
 Smith   F    19960212    24      1     SING

There may not be any discharge data yet but, I would like for the fields to appear as NULL.

You seem to have quite a bit of redundancy in your tables (e. g. `No, Name` and `DBO` are in both `member_info` and `auth_info`) which you should try to reduce. It might cause you a head-ache at some point otherwise. — Carsten Massmann, Aug 17 '15 at 19:26

score 1 · Answer 1 · edited May 23 '17 at 10:27

Beside Null fields there is solution if there is no discharge data then there may be no entries. you should use left join to combine table 'discharge_information'. sql query like:

Select    a.no, 
          a.Name, 
          a.Sex, 
          a.DBO, 
          a.CaseNo, 
          a.SeqNum,
          a.StartDate,
          b.Admin_1,
          b.Admin_2,
          b.Admin_3,
          c.DisDate,
          c.DisRea
 from     dbo.mem_information as a inner join dbo.auth_information as b on b.caseno = a.caseno AND 
          b.seqnum = a.seqnum AND        
          b.StartDate = a.StartDate 
          left join dbo.discharge_information as c ON c.caseno = b.caseno AND
          c.seqnum = b.seqnum;

see here usage for joins LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

score 1 · Accepted Answer · answered Aug 17 '15 at 19:25

1

Not sure exactly how to test this since you haven't provided any sample data, but I am assuming you are looking for something like this:

CREATE VIEW dbo.VIEW_NAME AS
SELECT a.[no]
    ,a.NAME
    ,a.Sex
    ,a.DBO
    ,a.CaseNo
    ,a.SeqNum
    ,MIN(a.StartDate) StartDate
    ,MAX(b.Admin_1) Admin_1
    ,MAX(b.Admin_2) Admin_2
    ,MAX(b.Admin_3) Admin_3
    ,MAX(c.DisDate) DisDate
    ,MAX(c.DisRea) DisRea
FROM dbo.mem_information AS a
INNER JOIN dbo.auth_information AS b ON b.caseno = a.caseno
    AND b.seqnum = a.seqnum
    AND b.StartDate = a.StartDate
LEFT JOIN dbo.discharge_information AS c ON c.caseno = b.caseno
    AND c.seqnum = b.seqnum
GROUP BY  a.[no]
    ,a.NAME
    ,a.Sex
    ,a.DBO
    ,a.CaseNo
    ,a.SeqNum;

answered Aug 17 '15 at 19:25

FutbolFan

13,235
3
23
35

1

This will probably work well, if there is at max. *one* value per casenum/seqnum and for each of the columns `Admin1, Admin2, Admin3, DisRate` and `Disrea`. If there should be more values floating around you would need another approach. – Carsten Massmann Aug 17 '15 at 19:33
@cars10 Definitely agree with you there! Yeah I think the OP has not clearly described his requirements. So, I am making a lot of assumptions here. – FutbolFan Aug 17 '15 at 19:35
Okay I made the changes and everything works except for the left join. It just loops for hours. – tnlewis Aug 18 '15 at 19:02
I finally got back to the query and got it working thanks. Now my only issue is that the query is slow. How can I create a view and index on this query to speed it up? – tnlewis Sep 16 '15 at 13:19
@tnbumbray The above code should have already created a view for you. As far as the performance goes, maybe you could start by creating an index on `StartDate` column. Usually, date joins are very troublesome. – FutbolFan Sep 16 '15 at 14:34