|
Subject:
|
Difference between recorsets
|
|
Posted By:
|
aware
|
Post Date:
|
8/2/2006 10:19:53 AM
|
Hi
I have 2 recordsets with the same fields-
Name, ID, Year_ID, Week_ID
No PK the 3 ID's make up the unique record.
How can I just return the difference bewteen the 2 recordsets?
ANdy
|
|
Reply By:
|
Peso
|
Reply Date:
|
8/2/2006 12:50:09 PM
|
-- prepare test data
declare @table1 table (Name varchar(10), ID int, Year_ID int, Week_ID int)
insert @table1
select 'Peso 1', 1, 1, 1 union all
select 'Peso 2', 2, 1, 5
declare @table2 table (Name varchar(10), ID int, Year_ID int, Week_ID int)
insert @table2
select 'Jennie A', 1, 1, 1 union all
select 'Jennie B', 2, 1, 2
-- do the work
SELECT CASE
WHEN t1.id = t2.id THEN 'Equal rows'
WHEN t1.id IS NULL THEN 'Only in table 2'
WHEN t2.id IS NULL THEN 'Only in table 1'
END Uniqueness,
ISNULL(t1.id, t2.id) id,
ISNULL(t1.year_id, t2.year_id) year_id,
ISNULL(t1.week_id, t2.week_id) week_id
FROM @table1 t1
FULL JOIN @table2 t2 ON t2.id = t1.id and t2.year_id = t1.year_id and t2.week_id = t1.week_id
ORDER BY 1, 2, 3, 4
|
|
Reply By:
|
aware
|
Reply Date:
|
8/4/2006 7:07:03 AM
|
Thanks I'll give this a try.
What I'm doing now is concatenating all the ID's to give me 1 id in each recordset and performing a NOT IN. Seems to work but not the most efficient I'll grant you.
Andy
|
|
Reply By:
|
Jeff Mason
|
Reply Date:
|
8/4/2006 7:27:02 AM
|
quote:
Originally posted by aware
How can I just return the difference bewteen the 2 recordsets?
Just so we're clear, what exactly do you mean by the 'difference' between those two sets?
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
|
|
Reply By:
|
aware
|
Reply Date:
|
8/8/2006 4:03:57 AM
|
Hi
I have 2 recordsets, 1 contains a list of suppliers to be rated, and the other a list of those already rated.
I want a list of those that should be rated but nave no rating, ie the difference between the 2 recordsets.
Using 'NOT IN' works OK, but the problem I have is that there is no PK on the data, only compound keys, so what I have done is concatenate the 3 id's to give a PK that I can use on the NOT IN.
Is there a way to use the NOT IN with mor than 1 id field?
Thanks
Andy
|
|
Reply By:
|
Jeff Mason
|
Reply Date:
|
8/11/2006 11:19:07 AM
|
Hi,
Sorry for my delay in responding, but the customer comes first. 
As I understand your requirement, you are looking for the entries in one table which do not have corresponding entries in another. There is more than one way to do this.
One way is via an OUTER JOIN:
SELECT T1.Name, T1.ID, T1.Year_ID, T1.Week_ID
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.ID = T2.ID
AND T1.Year_ID = T2.Year_ID
AND T1.Week_ID = T2.Week_ID
WHERE T2.ID IS NULL
AND T2.Year_ID IS NULL
AND T2.Week_ID IS NULL
This works because the LEFT JOIN on the two tables will result in NULL in the result for the column values in Table2 where there are no rows matching the JOIN condition in Table2.
From that result, we select only those rows whose Table2 values are NULL, meaning there is no Table2 row with those ID values.
Another way to do this is via a correlated subquery, using the NOT EXISTS test to select rows in Table1 which have no matching rows in Table2:
SELECT T1.Name, T1.ID, T1.Year_ID, T1.Week_ID
FROM Table1 T1
WHERE NOT EXISTS(SELECT * FROM Table2 T2
WHERE T2.ID = T1.ID
AND T2.Year_ID = T1.Year_ID
AND T2.Week_ID = T1.Week_ID)
Here we attempt to select a matching row in Table2 using the Table1 values and then select values from Table1 where the result of the attempted match has no rows, i.e. no matching rows in Table2.
I can't say which way is "better" - indeed, it's quite possible there would be no difference in the execution plans...
BTW, The fact that you have no "primary key" (a composite key is a perfectly good key) really has nothing to do with it, and wouldn't really help even if you had one, except to make the JOIN condition a little easier to write (maybe).
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
|
|
Reply By:
|
aware
|
Reply Date:
|
8/18/2006 7:54:56 AM
|
Brilliant thanks, this is just what I need.
I'll let you know how Iget on.
Andy
|
