In this table...and this table.....and that table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

In this table...and this table.....and that table?

Author

Topic

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-03-12 : 07:06:53

Hi,

I have 3 tables (tablea, tableb, tablec) containing customer info. Each customer is identified in each table via a customer_id field. What I want to do is find which customers exist in all three tables, and then possibly which customers exist in tablea and tableb but not tablec.

How can I achieve this? Is it possible to do it with one query?

Any info would be greatly appreciated.

Cheers

Paul

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-03-12 : 07:31:05

I think I've found a way to do it! My query is as follows:

select distinct customer_id
from tablea
where customer_id in
(select customer_id
from tableb)
and customer_id in
(select customer_id
from tablec)

If I wanted to find those who are in two tables but not the other I would change the "and customer_id in" to "and customer_id not in".

Does this look correct?

Cheers

Paul

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-03-12 : 08:17:49

I have taken some different scenario.
HTH..

use tempdb
go
create table A (x int)
create table B (y int)
create table C (z int)

insert into A values (1)
insert into B values (1)
insert into C values (1)
insert into A values (2)
insert into B values (2)
insert into A values (3)
insert into A values (4)
insert into B values (4)
insert into C values (4)

SELECT CASE A.x WHEN C.z THEN A.x ELSE NULL END AS 'ExistsIn3Tables',
CASE A.x WHEN C.z THEN NULL ELSE A.x END AS 'ExistsInTablesA&B'
FROM A INNER JOIN B ON A.x=B.y LEFT OUTER JOIN C ON A.x = C.z

___________________________________________________________________________
Success is not a destination that you ever reach. Success is the quality of your journey.

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-12 : 11:30:26

This should give you everything you need. You can then evaluate the results however you want:

CREATE TABLE Tablea (id int)
Go
CREATE TABLE Tableb (id int)
Go
CREATE TABLE Tablec (id int)
Go

Insert Into Tablea (id) Values (1)
Insert Into Tableb (id) Values (1)
Insert Into Tablec (id) Values (1)
Insert Into Tablea (id) Values (2)
Insert Into Tableb (id) Values (3)
Insert Into Tablec (id) Values (4)
Go

SELECT *
FROM Tablea a
FULL OUTER JOIN Tableb b
ON a.id = b.id
FULL OUTER JOIN Tablec c
ON b.id = c.id

DROP TABLE Tablea
Go
DROP TABLE Tableb
Go
DROP TABLE Tablec
Go

Brett

8-)

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-03-12 : 11:32:47

Thanks alot, I'll give it a try!!

Cheers

Paul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources