[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

join query help

Posted on 2007-04-09
4
Medium Priority
?
156 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:Brendt Hess
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:
Brendt Hess earned 2000 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

649 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