Compare 3 tables - SQL Server Forums

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

SQL Server Development (2000)

Compare 3 tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-05-16 : 09:38:12

nehemia writes "Canyou Help give the syntax ,
How can compare 3 tables with the record different ?
Example :
Table A and Table B and Table C

Table A is Record
NO. Name
001 Paper
002 Fish
003 Stick

Table B is Record
NO. Name
001 Paper
003 Stick

Table C is Record
NO. Name
001 Paper
003 Stick
004 Pen

Output
Table B diffrent with Table A is 002 Fish
Table C diffrent with Table A is 002 Fish
Table A diffrent with Table C is 004 Pen
Table B diffrent with Table C is 004 Pen

Can you give the syntax for me.

Thank You
"

ToddV
Posting Yak Master

218 Posts

Posted - 2003-05-16 : 10:27:34

Nehemia,

No good way to create the record set you desire comes to mind. You could Union together the results of a comparison A->B and A->C and
B->C.

Perhaps you will find this query useful.

CREATE TABLE #TBLA(
ID INT,
Name Varchar(10))

CREATE TABLE #TBLB(
ID INT,
Name Varchar(10))

CREATE TABLE #TBLC(
ID INT,
Name Varchar(10))

INSERT #TBLA
SELECT 001, 'Paper'
UNION ALL SELECT 002, 'Fish'
UNION ALL SELECT 003, 'Stick'

INSERT #TBLB
SELECT 001, 'Paper'
UNION ALL SELECT 003, 'Stick'

INSERT #TBLC
SELECT 001, 'Paper'
UNION ALL SELECT 003, 'Stick'
UNION ALL SELECT 004, 'Pen'

SELECT COALESCE(A.ID, B.ID, C.ID),
COALESCE(A.Name, B.Name, C.Name),
CASE WHEN A.ID IS NULL THEN 'N' ELSE 'Y' END AS "InTableA",
CASE WHEN B.ID IS NULL THEN 'N' ELSE 'Y' END AS "InTableB",
CASE WHEN C.ID IS NULL THEN 'N' ELSE 'Y' END AS "InTableC"
FROM #TBLA A
FULL OUTER JOIN #TBLB B
ON A.ID = B.ID
FULL OUTER JOIN #TBLC C
ON A.ID = C.ID

Returns
InTableA InTableB InTableC
----------- ---------- -------- -------- --------
1 Paper Y Y Y
2 Fish Y N N
3 Stick Y Y Y
4 Pen N N Y

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-16 : 11:11:45

Nice one, Todd ... good idea, very simplified approach.

I prefer UNION's to FULL OUTER JOIN's in this case, though:

Select ID, Name,
SUM(InTableA) as InTableA,
sUM(InTableB) as InTableB,
SUM(InTableC) as InTableC
FROM
(
SELECT ID, Name, 1 as InTableA, 0 as InTableB, 0 as InTableC
FROM #TBLA
union all
SELECT ID, Name, 0 as InTableA, 1 as InTableB, 0 as InTableC
FROM #TBLB
union all
SELECT ID, Name, 0 as InTableA, 0 as InTableB, 1 as InTableC
FROM #TBLC
)
a
GROUP BY ID, Name

Should have a better performance ... not sure, check it out. Logically, I feel FULL OUTER JOIN's are a little unsound because they return Nulls where primary key's should be.

- Jeff

Edited by - jsmith8858 on 05/16/2003 11:12:24

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources