SQLTeam.com | Weblogs | Forums

Comparing values in 3 tables


#1

Good Morning.
How would one go about creating a SQL query that will compare values in 3 tables and then return the AccountNumber and the tablename that the AccountNumber is in?

I'm trying to come up with a solution where the result lists the AccountNumbers and table name(s) that the AccountNumber appears in.....

Any help is greatly appreciated


#2

Can you post some DDL statements and what you have put together so far? I am not entirely sure of what you need. Is it a LEFT JOIN on the AccountNumber across the three tables or no JOIN at all but just to see if the AccountNumber exists in general?

Examples of DDL would be something like this:

DECLARE @TableName1 TABLE
(
AccountNumber CHAR(5) PRIMARY KEY
);
DECLARE @TableName2 TABLE
(
AccountNumber CHAR(5) PRIMARY KEY
);
DECLARE @TableName3 TABLE
(
AccountNumber CHAR(5) PRIMARY KEY
);

INSERT INTO @TableName1
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3';

INSERT INTO @TableName2
SELECT '1' UNION ALL
SELECT '4';

INSERT INTO @TableName3
SELECT '2' UNION ALL
SELECT '5';

SELECT *
FROM @TableName1;

SELECT *
FROM @TableName2;

SELECT *
FROM @TableName3;


#3

James, I am actually importing data from csv files. Here's what I have so far, I found this when i was researching...but i still can't figure out how to get the table names to display...

select AccountNumber
from (
select '1' as tableName, AccountNumber
from BPMVendors
union
select '2', AccountNumber
from DataDescription
union
select '3', AccountNumber
from Portfolios
) as ResultTable
group
by AccountNumber
having count(*) < 3