Comparing 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

General SQL Server Forums

New to SQL Server Programming

Comparing 3 Tables

Author

Topic

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-09-29 : 15:04:46

I have three tables that I need to compare data. Two tables retrieve static information from a live feed. However their column names are different:

FaISIR11
FaFAFSA11

I need to analyze the data from the first two tables and compare it to the data stored in a separate table called:

SyStudent

Three tables, all have different column names. I need to know if anyone has any suggestions on how to build a view or a query that will allow me to compare all of this data.

Thank you.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-30 : 02:59:18

Please give table structures, sample data and wanted output.

No, you're never too old to Yak'n'Roll if you're too young to die.

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-09-30 : 09:18:24

SELECT TOP (100) PERCENT dbo.syStudent.LastName, dbo.syStudent.FirstName, dbo.syStudent.Addr1, dbo.syStudent.City, dbo.syStudent.State,
dbo.syStudent.Zip, dbo.FaISIR11.LastName AS Expr1, dbo.FaISIR11.FirstName AS Expr2
FROM dbo.syStudent INNER JOIN
dbo.FaFAFSA11 ON dbo.syStudent.SyStudentId = dbo.FaFAFSA11.SyStudentID CROSS JOIN
dbo.FaISIR11
ORDER BY dbo.syStudent.LastName, dbo.syStudent.FirstName

Here is what I started building as a view for all three tables. Of course this is bringing back duplicate information from all three tables.

I think I need some sort of EXCEPT statement as well???

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-30 : 09:35:28

quote:
Originally posted by webfred

Please give table structures, sample data and wanted output.

No, you're never too old to Yak'n'Roll if you're too young to die.

Is it possible?

No, you're never too old to Yak'n'Roll if you're too young to die.

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-09-30 : 09:58:09

Here is what I was working on this morning to see if I can pull the data from 3 tables.

SELECT DISTINCT
dbo.syStudent.LastName,
dbo.syStudent.FirstName,
dbo.syStudent.Addr1,
dbo.syStudent.City,
dbo.syStudent.State,
dbo.syStudent.Zip
FROM
dbo.SyStudent
EXCEPT
SELECT DISTINCT
dbo.FaFAFSA11.LastName,
dbo.FaFAFSA11.FirstName,
dbo.FaFAFSA11.Address,
dbo.FaFAFSA11.City,
dbo.FaFAFSA11.State,
dbo.FaFAFSA11.Zip
FROM
dbo.FaFAFSA11
EXCEPT
SELECT DISTINCT
dbo.FaISIR11.LastName,
dbo.FaISIR11.FirstName,
dbo.FaISIR11.Address,
dbo.FaISIR11.City,
dbo.FaISIR11.State,
dbo.FaISIR11.Zip
FROM
dbo.FaISIR11

This works but I am still getting duplicate data.

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-09-30 : 10:00:15

Then I added a Union All hoping that would resolve some of the duplicates:

UNION ALL
SELECT DISTINCT
dbo.syStudent.LastName,
dbo.syStudent.FirstName,
dbo.syStudent.Addr1,
dbo.syStudent.City,
dbo.syStudent.State,
dbo.syStudent.Zip
FROM
dbo.SyStudent

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources