Solved

join query help

Posted on 2007-04-09
4
154 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

728 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