CODEWITHSUNDEEP
sqlinner-joinouter-joinjoin
tnlewis
tnlewis

Reputation: 323

Create a view by joining three tables

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.

Upvotes: 1

Views: 3613

Answers (2)

FutbolFan
FutbolFan

Reputation: 13713

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;

Upvotes: 1

Hafiz Arslan
Hafiz Arslan

Reputation: 473

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

Upvotes: 1

Related Questions