[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 421
  • Last Modified:

JOIN 4 tables

Three tables (PTBB, PTBBARC, and PTBBUpdateARC) have the same fields below:

MRN,
LName,
FName,
DOB,
Doctor,
ABO, RH,
Antigen,
Antibody

These three tbles need to be joined with the table, ptautoArc.

select
mrnauto, BloodReceived, DateEnterAuto
from ptautoArc

MRN and mrnauto are the keys for the join.

The information (MRN, etc......, Antibody) can exit on any of the tables or all of the tables.

If the information exists on PTBB, then use PTBB and ignore the other two for the join,
if the information exists on PTBBARC, then use PTBBARC and ignore the other two for the join,
otherwise, use PTBBUpdateARC and ignore the other two for the join.

ptautoArc has 100 records, so the output should return 100 records.
0
JohnLucania
Asked:
JohnLucania
  • 8
  • 5
  • 2
  • +1
3 Solutions
 
Vadim RappCommented:
select <what you aready have>,
isnull(ptbb.doctor , isnull(PTBBARC.doctor, PTBBUpdateARC.doctor)) ,
<some other fields from ptbb etc. that you need>

from plutoarc
left outer join PTBB on plautoArc.MRN = PTBB.MRN and plutearc.mrnauto=PTBB.mrnauto
left outer join PTBBARC on <as above>
left outer join PTBBUpdateARC on <as above>

0
 
David ToddSenior DBACommented:
Hi,

My thoughts are to join all the tables and then use isnull to display the fields you want.

A simple way that may work depending on what is in the other tables - if the joins are one to zero or one ...

For instance

select
    COALESCE( ptbb.LName, ptbbArc.LName, ptbUpdateArc.LName, '' ) as LName,
mrnauto, BloodReceived, DateEnterAuto
from ptautoArc
left outer join ptbb
    on ptautoArc.mrnauto = ptbb.mrn
left outer join ptbbArc
    on ptautoArc.mrnauto = ptbbArc.mrn
left outer join ptbbUpdateArc
    on ptautoArc.mrnauto = ptbbUpdateArc.mrn

Coalesce returns the _first_ non-null response. This meets your requirements to use ptbb then ptbbArc, then ptbbUpdateArc.

Unfortunately I don't see how to write this into the join itself, as you are making a judgement on a row by row basis and not on a set basis.

Regards
  David
0
 
JohnLucaniaAuthor Commented:
select
MRN,
LName,
FName,
DOB,
Doctor,
ABO,
RH,
Antigen,
Antibody,
isnull(ptbb.doctor , isnull(PTBBARC.doctor, PTBBUpdateARC.doctor))

from ptautoArc
left outer join PTBB on ptautoArc.MRN = PTBB.MRN and PTBBUpdateARC.mrnauto=PTBB.mrnauto
left outer join PTBBARC on  ptautoArc.MRN = PTBB.MRN and PTBBUpdateARC.mrnauto=PTBB.mrnauto
left outer join PTBBUpdateARC on  ptautoArc.MRN = PTBB.MRN and PTBBUpdateARC.mrnauto=PTBB.mrnauto

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'PTBBUpdateARC' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'PTBBUpdateARC' does not match with a table name or alias name used in the query.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
David ToddSenior DBACommented:
Hi,

Suggestions
1. Try my query above. I've used different joins - MRN = MRNAuto

2. Try this join ...

from ptautoArc
left outer join PTBB on ptautoArc.MRN = PTBB.MRN and ptautoArc.mrnauto=PTBB.mrnauto
left outer join PTBBARC on  ptautoArc.MRN = PTBB.MRN and ptautoArc.mrnauto=PTBBARC .mrnauto
left outer join PTBBUpdateARC on  ptautoArc.MRN = PTBB.MRN and ptautoArc.mrnauto=PTBBUpdateARC .mrnauto

Regards
  David

0
 
JohnLucaniaAuthor Commented:
select
COALESCE( ptbb.MRN, ptbbArc.MRN, ptbbUpdateArc.MRN, '' ) as MRN,
COALESCE( ptbb.LName, ptbbArc.LName, ptbbUpdateArc.LName, '' ) as LName,
COALESCE( ptbb.FName, ptbbArc.FName, ptbbUpdateArc.FName, '' ) as FName,
COALESCE( ptbb.dob, ptbbArc.dob, ptbbUpdateArc.dob, '' ) as dob,
COALESCE( ptbb.Doctor, ptbbArc.Doctor, ptbbUpdateArc.Doctor, '' ) as doctor,
COALESCE( ptbb.ABO, ptbbArc.ABO, ptbbUpdateArc.ABO, '' ) as ABO,
COALESCE( ptbb.RH, ptbbArc.RH, ptbbUpdateArc.RH, '' ) as RH,
COALESCE( ptbb.Antigen, ptbbArc.Antigen, ptbbUpdateArc.Antigen, '' ) as Antigen,
COALESCE( ptbb.Antibody, ptbbArc.Antibody, ptbbUpdateArc.Antibody, '' ) as Antibody,

mrnauto,
BloodReceived,
DateEnterAuto
from ptautoArc
left outer join ptbb
    on ptautoArc.mrnauto = ptbb.mrn
left outer join ptbbArc
    on ptautoArc.mrnauto = ptbbArc.mrn
left outer join ptbbUpdateArc
    on ptautoArc.mrnauto = ptbbUpdateArc.mrn
order by lname

This returns 313 records.

I need 100 records, which is the # of records on ptautoArc
0
 
JohnLucaniaAuthor Commented:
select
MRN,
LName,
FName,
DOB,
Doctor,
ABO,
RH,
Antigen,
Antibody,
isnull(ptbb.doctor , isnull(PTBBARC.doctor, PTBBUpdateARC.doctor))

from ptautoArc
left outer join PTBB on ptautoArc.MRN = PTBB.MRN and ptautoArc.mrnauto=PTBB.mrnauto
left outer join PTBBARC on  ptautoArc.MRN = PTBB.MRN and ptautoArc.mrnauto=PTBBARC.mrnauto
left outer join PTBBUpdateARC on  ptautoArc.MRN = PTBB.MRN and ptautoArc.mrnauto=PTBBUpdateARC.mrnauto

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'MRN'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'mrnauto'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'MRN'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'mrnauto'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'MRN'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'mrnauto'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'MRN'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'LName'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'FName'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'DOB'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Doctor'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'ABO'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'RH'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Antigen'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Antibody'.
0
 
David ToddSenior DBACommented:
Hi John,

With duplicate field/column structure across three tables, this is what you would expect.

Regards
  David

PS Try my query!
0
 
JohnLucaniaAuthor Commented:
Do you mean this?

select
COALESCE( ptbb.MRN, ptbbArc.MRN, ptbbUpdateArc.MRN, '' ) as MRN,
COALESCE( ptbb.LName, ptbbArc.LName, ptbbUpdateArc.LName, '' ) as LName,
COALESCE( ptbb.FName, ptbbArc.FName, ptbbUpdateArc.FName, '' ) as FName,
COALESCE( ptbb.dob, ptbbArc.dob, ptbbUpdateArc.dob, '' ) as dob,
COALESCE( ptbb.Doctor, ptbbArc.Doctor, ptbbUpdateArc.Doctor, '' ) as doctor,
COALESCE( ptbb.ABO, ptbbArc.ABO, ptbbUpdateArc.ABO, '' ) as ABO,
COALESCE( ptbb.RH, ptbbArc.RH, ptbbUpdateArc.RH, '' ) as RH,
COALESCE( ptbb.Antigen, ptbbArc.Antigen, ptbbUpdateArc.Antigen, '' ) as Antigen,
COALESCE( ptbb.Antibody, ptbbArc.Antibody, ptbbUpdateArc.Antibody, '' ) as Antibody,
isnull(ptbb.doctor , isnull(PTBBARC.doctor, PTBBUpdateARC.doctor))

from ptautoArc
left outer join PTBB on ptautoArc.MRN = PTBB.MRN and ptautoArc.mrnauto=PTBB.mrnauto
left outer join PTBBARC on  ptautoArc.MRN = PTBB.MRN and ptautoArc.mrnauto=PTBBARC.mrnauto
left outer join PTBBUpdateARC on  ptautoArc.MRN = PTBB.MRN and ptautoArc.mrnauto=PTBBUpdateARC.mrnauto

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'MRN'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'mrnauto'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'MRN'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'mrnauto'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'MRN'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'mrnauto'.

0
 
JohnLucaniaAuthor Commented:
SELECT

COALESCE( PTBB.MRN, PTBBARC.MRN, PTBBUPDATEARC.MRN, '' ) AS MRN,
COALESCE( PTBB.LNAME, PTBBARC.LNAME, PTBBUPDATEARC.LNAME, '' ) AS LNAME,
COALESCE( PTBB.FNAME, PTBBARC.FNAME, PTBBUPDATEARC.FNAME, '' ) AS FNAME,
COALESCE( PTBB.DOB, PTBBARC.DOB, PTBBUPDATEARC.DOB, '' ) AS DOB,
COALESCE( PTBB.DOCTOR, PTBBARC.DOCTOR, PTBBUPDATEARC.DOCTOR, '' ) AS DOCTOR,
COALESCE( PTBB.ABO, PTBBARC.ABO, PTBBUPDATEARC.ABO, '' ) AS ABO,
COALESCE( PTBB.RH, PTBBARC.RH, PTBBUPDATEARC.RH, '' ) AS RH,
COALESCE( PTBB.ANTIGEN, PTBBARC.ANTIGEN, PTBBUPDATEARC.ANTIGEN, '' ) AS ANTIGEN,
COALESCE( PTBB.ANTIBODY, PTBBARC.ANTIBODY, PTBBUPDATEARC.ANTIBODY, '' ) AS ANTIBODY,
ISNULL(PTBB.MRN , ISNULL(PTBBARC.MRN, PTBBUPDATEARC.MRN)),
ISNULL(PTBB.LNAME , ISNULL(PTBBARC.LNAME, PTBBUPDATEARC.LNAME)),
ISNULL(PTBB.FNAME , ISNULL(PTBBARC.FNAME, PTBBUPDATEARC.FNAME)),
ISNULL(PTBB.DOB , ISNULL(PTBBARC.DOB, PTBBUPDATEARC.DOB)),
ISNULL(PTBB.DOCTOR , ISNULL(PTBBARC.DOCTOR, PTBBUPDATEARC.DOCTOR)),
ISNULL(PTBB.ABO , ISNULL(PTBBARC.ABO, PTBBUPDATEARC.ABO)),
ISNULL(PTBB.RH , ISNULL(PTBBARC.RH, PTBBUPDATEARC.RH)),
ISNULL(PTBB.ANTIGEN , ISNULL(PTBBARC.ANTIGEN, PTBBUPDATEARC.ANTIGEN)),
ISNULL(PTBB.ANTIBODY , ISNULL(PTBBARC.ANTIBODY, PTBBUPDATEARC.ANTIBODY))
FROM PTAUTOARC
LEFT OUTER JOIN PTBB ON PTAUTOARC.MRNAUTO = PTBB.MRN AND PTAUTOARC.MRNAUTO=PTBB.MRN
LEFT OUTER JOIN PTBBARC ON  PTAUTOARC.MRNAUTO = PTBB.MRN AND PTAUTOARC.MRNAUTO=PTBBARC.MRN
LEFT OUTER JOIN PTBBUPDATEARC ON  PTAUTOARC.MRNAUTO = PTBB.MRN AND PTAUTOARC.MRNAUTO=PTBBUPDATEARC.MRN

I think you meant this, but it retruns 160 records.
0
 
David ToddSenior DBACommented:
Hi John,

Then your ptbb, ptbbarc, ptbbupdatearc tables have more than 1 record for each record in ptautoarc.

Can you check/limit the data, as the join starts to get ugly pretty quickly.

Regards
  David
0
 
JohnLucaniaAuthor Commented:
ok, I came up with:

insert into PTBBUNION
select
MRN, LName, FName, DOB, Doctor, Gender, ABO, RH, Antigen, Antibody, DateEnter
from ptbb
union
select
MRN, LName, FName, DOB, Doctor, Gender, ABO, RH, Antigen, Antibody, DateEnter
from ptbbArc
union
select
MRN, LName, FName, DOB, Doctor, Gender, ABO, RH, Antigen, Antibody, DateUpdate
from PTBBUpdateARC
order by lname, fname

but, I have more than one record for the same MRN

select MRN, count(MRN)
from dbo.PTBBUNION
group by MRN
having count(MRN) > 1

I want to return the lastet record for the same MRN so that records with unique MRN can be returned.  DateEnter filed has the date.
How to do?
0
 
JohnLucaniaAuthor Commented:
obvioulsy, this is not the right query.

select a.*
from PTBBUNION a, PTBBUNION b
where (a.DateEnter > b.DateEnter) and (a.MRN = b.MRN)
0
 
David ToddSenior DBACommented:
Hi John,

Something like this should work

select top 99 FixedDriveData.*
from dbo.FixedDriveData
inner join
      (
      select
            Drive,      
            max( CreateDate ) LatestCreateDate
      from
            dbo.FixedDriveData
      group  by Drive
      ) LatestData
      on FixedDriveData.Drive = LatestData.Drive
      and FixedDriveData.CreateDate = LatestData.LatestCreateDate

select *
from ptautoArc
left outer join
    (
    select *
    from ptbb
    inner join
        (
        select MRN, max( DateEnterAuto ) LatestDateEnterAuto
        from ptbb
        group by MRN
        ) LatestData
    on ptbb.MRM = LatestData.MRM
    and ptbb.DateEnterAuto = LatestData.LatestDateEnterAuto
    ) ptbb_f
on ptautoArc.MRN = ptbb_f.MRN
etc ...

HTH

Regards
  David
0
 
JohnLucaniaAuthor Commented:
select *
from ptautoArc
left outer join
    (
    select *
    from ptbbunion
    inner join
        (
        select MRN, max( DateEnter ) LatestDateEnterAuto
        from ptbbunion
        group by MRN
        ) LatestData
    on ptbbunion.MRN = LatestData.MRN
    and ptbbunion.DateEnter = LatestData.LatestDateEnterAuto
    ) ptbb_f
on ptautoArc.MRNAuto = ptbb_f.MRN

Server: Msg 8156, Level 16, State 1, Line 1
The column 'MRN' was specified multiple times for 'ptbb_f'.

not sure what this error means.
0
 
techjoshCommented:
CREATE VIEW CompositData
AS
SELECT
        CASE
                WHEN T1.MRN IS NOT NULL THEN T1.MRN
                WHEN T2.MRN IS NOT NULL THEN T2.MRN
                ELSE T3.MRN
        END AS MRN,
        CASE
                WHEN T1.MRN IS NOT NULL THEN T1.LName
                WHEN T2.MRN IS NOT NULL THEN T2.LName
                ELSE T3.LName
        END AS LName,
        CASE
                WHEN T1.MRN IS NOT NULL THEN T1.FName
                WHEN T2.MRN IS NOT NULL THEN T2.FName
                ELSE T3.FName
        END AS FName,
        CASE
                WHEN T1.MRN IS NOT NULL THEN T1.DOB
                WHEN T2.MRN IS NOT NULL THEN T2.DOB
                ELSE T3.DOB
        END AS DOB,
        CASE
                WHEN T1.MRN IS NOT NULL THEN T1.Doctor
                WHEN T2.MRN IS NOT NULL THEN T2.Doctor
                ELSE T3.Doctor
        END AS Doctor,
        CASE
                WHEN T1.MRN IS NOT NULL THEN T1.ABO
                WHEN T2.MRN IS NOT NULL THEN T2.ABO
                ELSE T3.ABO
        END AS ABO,
        CASE
                WHEN T1.MRN IS NOT NULL THEN T1.RH
                WHEN T2.MRN IS NOT NULL THEN T2.RH
                ELSE T3.RH
        END AS RH,
        CASE
                WHEN T1.MRN IS NOT NULL THEN T1.Antigen
                WHEN T2.MRN IS NOT NULL THEN T2.Antigen
                ELSE T3.Antigen
        END AS Antigen,
        CASE
                WHEN T1.MRN IS NOT NULL THEN T1.Antibody
                WHEN T2.MRN IS NOT NULL THEN T2.Antibody
                ELSE T3.Antibody
        END AS Antibody
FROM
        PTBB AS T1
        FULL OUTER JOIN PTBBARC AS T2 ON T1.MRN = T2.MRN
        FULL OUTER JOIN PTBBUpdateARC AS T3 ON T1.MRN =T3.MRN AND T2.MRN = T3.MRN
GO


SELECT * FROM ptautoArc
INNER JOIN CompositeData ON ptautoArc.MRNAuto = CompositeData.MRN
0
 
techjoshCommented:
Just for completeness... after sleeping on it I realized there is a problem with the join in the view... it should be this:

FROM
        PTBB AS T1
        FULL OUTER JOIN PTBBARC AS T2 ON T1.MRN = T2.MRN
        FULL OUTER JOIN PTBBUpdateARC AS T3 ON T1.MRN =T3.MRN OR T2.MRN = T3.MRN  --changed AND to OR to allow rows with values for t1 or t2 only (not both) to properly join.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 8
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now