Solved

join query help

Posted on 2007-04-09
4
153 Views
Last Modified: 2010-03-19
I want to select Col1 from
tbl2, tbl3, and tbl4
where Col1 does not exist in
tbl1

how do i do that?
0
Comment
Question by:Mike Miller
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 32

Expert Comment

by:bhess1
ID: 18877923
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18878250
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
 

Author Comment

by:Mike Miller
ID: 18878568
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
 
LVL 32

Accepted Solution

by:
bhess1 earned 500 total points
ID: 18878872
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

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question