Comparing Table data oddities

[Dashley]

Hi,

I have two tables. I want to pull the MstrTab.ISBN, MstrTab.Title from MstrTab that dont exist in HPtab.ISBN.

MstrTab (419 rows) and HPtab (193 rows)
ISBN ISBN
Title

I've tried this statement:

SELECT mstrtab.ISBN, mstrtab.title
FROM mstrtab
WHERE mstrtab.ISBN NOT IN (SELECT HPtab.ISBN from HPtab )

It's returning 239 rows.

419 - 193 = 226 rows.

BTW mstrtab.ISBN = HPtab.ISBN


Question: Is this a good statement I'm using or is there a more accurate way?

I've tried and except clause

select ISBN from mstrtab
except
select ISBN from HPtab

and it's returning 238 rows. (I did find one duplicate ISBN in the mstrtab table)

Any Ideas ?

Thanks

-dan

 

[Andrzejek]

I'd say: if it gives you accurate results, then it is good.

I also use something like:[tt]
select ISBN from mstrtab
MINUS
select ISBN from HPtab[/tt]
and the other way around[tt]
select ISBN from HPtab
MINUS
select ISBN from mstrtab[/tt]

especially when I want to compare more than one field:
[tt]Select FieldA, FieldB, FieldC From TableA
MINUS
Select FieldX, FieldY, FieldZ From TableX[/tt]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[TamarGranor]

Both the approaches you show should work, so this is likely an issue with your data.

Tamar