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

-- 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

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

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

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

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:
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:
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

Brilliant thanks, this is just what I need.

I'll let you know how Iget on.

Andy