Problem with comparing 2 tables

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)

Problem with comparing 2 tables

Author

Topic

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2008-08-26 : 06:51:18

Need some help to solve this sql query -

Wanted to compare 2 tables and return records that do not matched with count 1 for unmatched and count 2 for matched.
The output shld filter and return unmatched records.

The GRADES field contain grades & NULL as value too.

Record Table
ID,
NAME,
YEAR_X1,
GRADES_X1,

profile Table
ID,
NAME,
YEAR_X2,
GRADES_X2,

SELECT MIN(Record) AS Record,
ID,
NAME,
YEAR,
GRADES,
FROM
(
SELECT Record AS Record,
ID,
NAME,
YEAR,
GRADES,
FROM Record a
WHERE NOT EXISTS
(SELECT 1 FROM profile b
WHERE a.ID = b.ID
AND isnull(a.ID,0) = isnull(b.ID,0)
AND isnull(a.NAME,'*') = isnull(b.NAME,'*')
AND isnull(a.YEAR,'*') = isnull(b.YEAR,'*')
AND isnull(a.GRADES,'*') = isnull(b.GRADES,'*')
)

UNION ALL

SELECT profile AS Record,
ID,
NAME,
YEAR,
GRADES,
FROM profile b
WHERE NOT EXISTS
(SELECT 1 FROM Record a
WHERE a.ID = b.ID
AND isnull(a.ID,0) = isnull(b.ID,0)
AND isnull(a.NAME,'*') = isnull(b.NAME,'*')
AND isnull(a.YEAR,'*') = isnull(b.YEAR,'*')
AND isnull(a.GRADES,'*') = isnull(b.GRADES,'*')
)
)
group by
ID,
NAME,
YEAR,
GRADES,
having count(*) = 1
order by ID;

Thanks In Advance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-26 : 07:01:53

[code]
SELECT * FROM
(SELECT COALESCE(r.ID,p.ID) AS ID,
COALESCE(r.Name,p.Name) AS Name,
COALESCE(r.YEAR_X1,p.YEAR_X2) AS YEAR,
COALESCE(r.GRADES_X1,p.GRADES_X2) AS GRADES,
SUM(CASE WHEN COALESCE(r.Name,'*') = COALESCE(p.Name,'*')
OR COALESCE(r.YEAR_X1,'*') = COALESCE(p.YEAR_X2,'*')
OR COALESCE(r.GRADES_X1,'*') = COALESCE(p.GRADES_X2,'*')
THEN 1 ELSE 0 END) AS match
FROM Record r
FULL OUTER JOIN Profile p
ON p.ID=r.ID)t
WHERE t.Match =0[/code]

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2008-08-26 : 07:38:21

There is error saying that it is not a single-group group function,
how can it be amended ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-26 : 08:19:15

where's error coming?

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2008-08-26 : 21:43:16

[quote]Originally posted by visakh16

[code]
(SELECT COALESCE(r.ID,p.ID) AS ID,

From the Line above after I execute sql script..Have added somemore fields for comparing..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 00:00:07

post the query used

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2008-08-27 : 02:02:38

Comparing some fields in both table. And - Would it be a better way to create a temp column to display the count while all the columns are displayed ?

SELECT * FROM
(SELECT COALESCE(r.ID,p.ID) AS ID,
COALESCE(r.NAME,p.NAME) AS NAME,
COALESCE(r.LEVEL, p.XLEVEL) AS LEVEL,
COALESCE(r.YEAR_X1,p.YEAR_X2) AS YEAR,
COALESCE(r.GRADES_X1,p.GRADES_X2) AS GRADES,
SUM(CASE WHEN COALESCE(r.NAME,'*') = COALESCE(p.NAME,'*')
OR COALESCE(r.LEVEL,'*') = COALESCE(p.XLEVEL,'*')
OR COALESCE(r.YEAR_X1,'*') = COALESCE(p.YEAR_X2,'*')
OR COALESCE(r.GRADES_X1,'*') = COALESCE(p.GRADES_X2,'*')
THEN 1 ELSE 0 END) AS match
FROM Record r
FULL OUTER JOIN Profile p
ON p.ID=r.ID)t
WHERE t.Match =0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 03:04:36

i missed a group by

SELECT * FROM
(SELECT COALESCE(r.ID,p.ID) AS ID,
COALESCE(r.NAME,p.NAME) AS NAME,
COALESCE(r.LEVEL, p.XLEVEL) AS LEVEL,
COALESCE(r.YEAR_X1,p.YEAR_X2) AS YEAR,
COALESCE(r.GRADES_X1,p.GRADES_X2) AS GRADES,
SUM(CASE WHEN COALESCE(r.NAME,'*') = COALESCE(p.NAME,'*')
OR COALESCE(r.LEVEL,'*') = COALESCE(p.XLEVEL,'*')
OR COALESCE(r.YEAR_X1,'*') = COALESCE(p.YEAR_X2,'*')
OR COALESCE(r.GRADES_X1,'*') = COALESCE(p.GRADES_X2,'*')
THEN 1 ELSE 0 END) AS match
FROM @test1 r
FULL OUTER JOIN @test2 p
ON p.ID=r.ID
GROUP BY COALESCE(r.ID,p.ID),COALESCE(r.NAME,p.NAME),
COALESCE(r.LEVEL, p.XLEVEL),COALESCE(r.YEAR_X1,p.YEAR_X2),
COALESCE(r.GRADES_X1,p.GRADES_X2)  )t
WHERE t.Match =0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 05:21:52

This will give unmatched records from both table

SELECT t.* FROM
(
SELECT *,'table1' AS SrcTable FROM @test1
UNION ALL 
select *,'table2' from @test2
)t
INNER JOIN 
(SELECT COALESCE(r.ID,p.ID) AS ID
FROM @test1 r
FULL OUTER JOIN @test2 p
ON p.ID=r.ID
GROUP BY COALESCE(r.ID,p.ID)
HAVING SUM(CASE WHEN COALESCE(r.NAME,'*') = COALESCE(p.NAME,'*')
OR COALESCE(r.LEVEL,'*') = COALESCE(p.XLEVEL,'*')
OR COALESCE(r.YEAR_X1,'*') = COALESCE(p.YEAR_X2,'*')
OR COALESCE(r.GRADES_X1,'*') = COALESCE(p.GRADES_X2,'*')
THEN 1 ELSE 0 END) =0 
)t1
on t1.ID=t.ID

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2008-08-27 : 06:20:46

shld the Match output be only '0' and unmatched be '1'?
Can i count the records separately in another column ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 06:24:58

quote:
Originally posted by melon.melon

shld the Match output be only '0' ?
Can i know it means that it is matched records with the other table?

it maens its those rows from both tables which cant be matched due to difference in values. Category designates which table they belong to.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 06:49:56

quote:
Originally posted by melon.melon

shld the Match output be only '0' and unmatched be '1'?
Can i count the records separately in another column ?

so you just want count of matched and unmatched?

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2008-08-27 : 07:06:04

so far you have separate the filtering of Matched and Unmatched data into 2..Wanted to do both retrieve filtering and count together if that is possible, if not i just need to add a count.
What is 'Table 1' & 'Table 2' suppose to mean ?
Cant count for unmatched..

Matched:

SELECT count (1)* FROM
(SELECT COALESCE(r.ID,p.ID) AS ID,
COALESCE(r.NAME,p.NAME) AS NAME,
COALESCE(r.LEVEL, p.XLEVEL) AS LEVEL,
COALESCE(r.YEAR_X1,p.YEAR_X2) AS YEAR,
COALESCE(r.GRADES_X1,p.GRADES_X2) AS GRADES,
SUM(CASE WHEN COALESCE(r.NAME,'*') = COALESCE(p.NAME,'*')
OR COALESCE(r.LEVEL,'*') = COALESCE(p.XLEVEL,'*')
OR COALESCE(r.YEAR_X1,'*') = COALESCE(p.YEAR_X2,'*')
OR COALESCE(r.GRADES_X1,'*') = COALESCE(p.GRADES_X2,'*')
THEN 1 ELSE 0 END) AS match
FROM @test1 r
FULL OUTER JOIN @test2 p
ON p.ID=r.ID
GROUP BY COALESCE(r.ID,p.ID),COALESCE(r.NAME,p.NAME),
COALESCE(r.LEVEL, p.XLEVEL),COALESCE(r.YEAR_X1,p.YEAR_X2),
COALESCE(r.GRADES_X1,p.GRADES_X2) )t
WHERE t.Match =0

----------------------------------------------------

Unmatched:

SELECT t.* FROM
(
SELECT *,'table1' AS SrcTable FROM @test1
UNION ALL
select *,'table2' from @test2
)t
INNER JOIN
(SELECT COALESCE(r.ID,p.ID) AS ID
FROM @test1 r
FULL OUTER JOIN @test2 p
ON p.ID=r.ID
GROUP BY COALESCE(r.ID,p.ID)
HAVING SUM(CASE WHEN COALESCE(r.NAME,'*') = COALESCE(p.NAME,'*')
OR COALESCE(r.LEVEL,'*') = COALESCE(p.XLEVEL,'*')
OR COALESCE(r.YEAR_X1,'*') = COALESCE(p.YEAR_X2,'*')
OR COALESCE(r.GRADES_X1,'*') = COALESCE(p.GRADES_X2,'*')
THEN 1 ELSE 0 END) =0
)t1
on t1.ID=t.ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 07:55:03

quote:
Originally posted by melon.melon

so far you have separate the filtering of Matched and Unmatched data into 2..Wanted to do both retrieve filtering and count together if that is possible, if not i just need to add a count.
What is 'Table 1' & 'Table 2' suppose to mean ?
Cant count for unmatched..

Matched:

SELECT count (1)* FROM
(SELECT COALESCE(r.ID,p.ID) AS ID,
COALESCE(r.NAME,p.NAME) AS NAME,
COALESCE(r.LEVEL, p.XLEVEL) AS LEVEL,
COALESCE(r.YEAR_X1,p.YEAR_X2) AS YEAR,
COALESCE(r.GRADES_X1,p.GRADES_X2) AS GRADES,
SUM(CASE WHEN COALESCE(r.NAME,'*') = COALESCE(p.NAME,'*')
OR COALESCE(r.LEVEL,'*') = COALESCE(p.XLEVEL,'*')
OR COALESCE(r.YEAR_X1,'*') = COALESCE(p.YEAR_X2,'*')
OR COALESCE(r.GRADES_X1,'*') = COALESCE(p.GRADES_X2,'*')
THEN 1 ELSE 0 END) AS match
FROM @test1 r
FULL OUTER JOIN @test2 p
ON p.ID=r.ID
GROUP BY COALESCE(r.ID,p.ID),COALESCE(r.NAME,p.NAME),
COALESCE(r.LEVEL, p.XLEVEL),COALESCE(r.YEAR_X1,p.YEAR_X2),
COALESCE(r.GRADES_X1,p.GRADES_X2) )t
WHERE t.Match =0

----------------------------------------------------

Unmatched:

SELECT t.* FROM
(
SELECT *,'table1' AS SrcTable FROM @test1
UNION ALL
select *,'table2' from @test2
)t
INNER JOIN
(SELECT COALESCE(r.ID,p.ID) AS ID
FROM @test1 r
FULL OUTER JOIN @test2 p
ON p.ID=r.ID
GROUP BY COALESCE(r.ID,p.ID)
HAVING SUM(CASE WHEN COALESCE(r.NAME,'*') = COALESCE(p.NAME,'*')
OR COALESCE(r.LEVEL,'*') = COALESCE(p.XLEVEL,'*')
OR COALESCE(r.YEAR_X1,'*') = COALESCE(p.YEAR_X2,'*')
OR COALESCE(r.GRADES_X1,'*') = COALESCE(p.GRADES_X2,'*')
THEN 1 ELSE 0 END) =0
)t1
on t1.ID=t.ID

table 1 and table 2 are for illustration.
So you want counts alone? Can you show format of output expected?

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2008-08-27 : 22:10:29

quote:

table 1 and table 2 are for illustration.
So you want counts alone? Can you show format of output expected?

Yup, would want to show both output expected of matched & unmatched records together with the counts of matched & unmatched records..is this possible ? It will be okay if the Matched & Unmatched are done in 2 separate query.

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2008-08-28 : 22:03:03

quote:

SELECT t.* FROM
(
SELECT *,'table1' AS SrcTable FROM Record
UNION ALL
select *,'table2' from profile
)t
INNER JOIN
(SELECT COALESCE(r.ID,p.ID) AS ID
FROM Record r
FULL OUTER JOIN profile p
ON p.ID=r.ID
GROUP BY COALESCE(r.ID,p.ID)
HAVING SUM(CASE WHEN COALESCE(r.NAME,'*') = COALESCE(p.NAME,'*')
OR COALESCE(r.LEVEL,'*') = COALESCE(p.XLEVEL,'*')
OR COALESCE(r.YEAR_X1,'*') = COALESCE(p.YEAR_X2,'*')
OR COALESCE(r.GRADES_X1,'*') = COALESCE(p.GRADES_X2,'*')
THEN 1 ELSE 0 END) =0
)t1
on t1.ID=t.ID

Was using the unmatched query and there was this error
'FROM keyword not found where expected'
from this line:
SELECT *,'table1' AS SrcTable FROM @test1

Is there any syntax missing anywhere ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-29 : 01:36:01

this will give you matched and unmatched counts

SELECT SUM(CASE WHEN Match=1 THEN 1 ELSE 0 END) AS MatchedCount,
SUM(CASE WHEN Match=0 THEN 1 ELSE 0 END) AS UnmatchedCount
FROM
(
SELECT COALESCE(r.ID,p.ID) AS ID,
 SUM(CASE WHEN COALESCE(r.NAME,'*') = COALESCE(p.NAME,'*')
AND COALESCE(r.LEVEL,'*') = COALESCE(p.XLEVEL,'*')
AND COALESCE(r.YEAR_X1,'*') = COALESCE(p.YEAR_X2,'*')
AND COALESCE(r.GRADES_X1,'*') = COALESCE(p.GRADES_X2,'*')
THEN 1 ELSE 0 END) AS Match
FROM Record r
FULL OUTER JOIN profile p
ON p.ID=r.ID
GROUP BY COALESCE(r.ID,p.ID)
)t

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-29 : 01:40:13

this will give matched and unmatched records

Matched
--------------

SELECT * FROM Record
WHERE ID IN(
SELECT COALESCE(r.ID,p.ID) AS ID
FROM Record r
FULL OUTER JOIN profile p
ON p.ID=r.ID
GROUP BY COALESCE(r.ID,p.ID)
HAVING SUM(CASE WHEN COALESCE(r.NAME,'*') = COALESCE(p.NAME,'*')
AND COALESCE(r.LEVEL,'*') = COALESCE(p.XLEVEL,'*')
AND COALESCE(r.YEAR_X1,'*') = COALESCE(p.YEAR_X2,'*')
AND COALESCE(r.GRADES_X1,'*') = COALESCE(p.GRADES_X2,'*')
THEN 1 ELSE 0 END) =1
)

Unmatched
------------------------------

SELECT * FROM 
(SELECT * FROM Record
UNION ALL
SELECT * FROM profile) t
WHERE t.ID IN(
SELECT COALESCE(r.ID,p.ID) AS ID
FROM Record r
FULL OUTER JOIN profile p
ON p.ID=r.ID
GROUP BY COALESCE(r.ID,p.ID)
HAVING SUM(CASE WHEN COALESCE(r.NAME,'*') = COALESCE(p.NAME,'*')
AND COALESCE(r.LEVEL,'*') = COALESCE(p.XLEVEL,'*')
AND COALESCE(r.YEAR_X1,'*') = COALESCE(p.YEAR_X2,'*')
AND COALESCE(r.GRADES_X1,'*') = COALESCE(p.GRADES_X2,'*')
THEN 1 ELSE 0 END) =0
)

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2008-09-01 : 03:16:57

Thank You for the solutions.. I did tried the unmatched but nt sure if is correct.
Have found easier way to compare..It will return the unmatched.

select * from Record a, profile b
where a.ID <> b.ID
and a.NAME <> b.NAME
and a.YEAR_X1 <> b.YEAR_X2
and a.GRADES_X1 <> b.GRADES_X2
and a.ID = b.ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources