Solved

join query help

Posted on 2007-04-09
4
149 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:mwmiller78
  • 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 142

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

864 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now