We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

JOIN 4 tables

JohnLucania
JohnLucania asked
on
Medium Priority
464 Views
Last Modified: 2012-05-05
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.
Comment
Watch Question

CERTIFIED EXPERT
Commented:
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>

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
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

Author

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.
David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
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

Author

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

Author

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'.
David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
Hi John,

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

Regards
  David

PS Try my query!

Author

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'.

Author

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.
David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
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

Author

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?

Author

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)
Senior Database Administrator
CERTIFIED EXPERT
Commented:
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

Author

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.
Commented:
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

Commented:
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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.