• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

join query help

I want to select Col1 from
tbl2, tbl3, and tbl4
where Col1 does not exist in
tbl1

how do i do that?
0
Mike Miller
Asked:
Mike Miller
  • 2
1 Solution
 
Brendt HessSenior DBACommented:
Assumption:  Col1 is the key used to join each of these tables to the others:

SELECT
    tbl2.Col1,
    tbl3.Col1,
    tbl4.Col1
FROM tbl2
INNER JOIN tbl3
    ON tbl2.Col1 = tbl3.Col1
INNER JOIN tbl4
    ON tbl3.Col1 = tbl4.Col1
LEFT JOIN tbl1
    ON tbl2.Col1 = tbl1.Col1
WHERE tbl1.Col1 IS NULL

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
my assumption:
select all the rows from the tables tbl2, tbl3 and tbl4 where respectively, the value of Col1 does not exist in tbl1.

select <fieldlist> from tbl2
where not exists ( select null from tbl1 where tbl1.col1 = tbl2.col1 )
union all
select <fieldlist> from tbl3
where not exists ( select null from tbl1 where tbl1.col1 = tbl3.col1 )
union all
select <fieldlist> from tbl4
where not exists ( select null from tbl1 where tbl1.col1 = tbl4.col1 )
 
so, in case neither suggestions work, you will have to make your requirements clearer, for example using sample data and requested output.
0
 
Mike MillerSoftware EngineerAuthor Commented:
Basically, I have an empty table. Users will enter data in an application. When they do this, the app will grab data from tbl2, 3, and 4, (including Col1) and place it in tbl1.

I want to query tbl1 to find out if the value of Col1 exists in both tbl1 and tbl2, 3, or 4. if a value is present in tbl2, 3, or 4, but not in tbl1...then it is returned in the result.

0
 
Brendt HessSenior DBACommented:
Okay, a bit different.  Try this:

INSERT INTO tbl1 (col1)
SELECT Col1 FROM (
    SELECT tbl2.Col1
    FROM tbl2
    LEFT OUTER JOIN tbl1
        ON tbl2.Col1 = tbl1.Col1
    WHERE tbl1.Col1 IS NULL

    UNION

    SELECT tbl3.Col1
    FROM tbl3
    LEFT OUTER JOIN tbl1
        ON tbl3.Col1 = tbl1.Col1
    WHERE tbl1.Col1 IS NULL

    UNION

    SELECT tbl4.Col1
    FROM tbl4
    LEFT OUTER JOIN tbl1
        ON tbl4.Col1 = tbl1.Col1
    WHERE tbl1.Col1 IS NULL
    ) As Src

This should identify all values missing from tbl1 but in any of the three tables.  Since a UNION by default returns a given row only once (no matter how many duplicates it originally had - like a DISTINCT query)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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