How to find missing records from tables involving composite primary keys

Table 1

Code	Quarter
500002	26
500002	27
500002	28
500002	28.5
500002	29

Table 2

Code	Qtr
500002	26
500002	27

I have these two identical tables with the columns CODE & Qtr being COMPOSITE PRIMARY KEYS

Can anybody help me with how to compare the two tables to find the records not present in Table 2

That is i need this result

Code	Quarter
500002	28
500002	28.5
500002	29

I have come up with this solution

select scrip_cd,Qtr,scrip_cd+Qtr from Table1 where
scrip_cd+Qtr not in (select scrip_cd+qtr as 'con' from Table2)

i need to know if there is some other way of doing the same

Thanks in Advance

Jacx

You can use the following query too...

Select
A.Code,
A.Quarter
From
[Table 1] A
Left Outer Join [Table 2] B on A.Code = B.Code And A.Quarter = B.Quarter
Where
B.Code is NULL

|||

Using NOT EXISTS is the fastest way to solve your problem. Use query below:

select t1.scrip_cd, t1.Qtr

from Table1 as t1

where not exists(

select * from Table2 as t2

where t2.scrip_cd = t1.scrip_cd and t2.Qtr = t1.Qtr

)

how to find duplicate data involving more than one field

How can I query a database that checks for duplicate data in a combination
of fields. For instance, LastName may have many duplicates but I want to
find duplicates of LastName combined with FirstName. Thanks.SELECT firstname, lastname
FROM YourTable
GROUP BY firstname, lastname
HAVING COUNT(*)>1
David Portas
SQL Server MVP
--|||This is great to show what is duplicated and by adding changing Select to co
unt(*) I was able to see how many times it was duplicated. Are you able to t
ake this 1 step further and actually return all duplicated and complete reco
rds? EG. John,Smith is duplicated 3 times but the City is different in each
case. Can you return the 3 first,last,city records?
Robert Lassiter

quote:

---

Originally posted by David Portas
SELECT firstname, lastname
FROM YourTable
GROUP BY firstname, lastname
HAVING COUNT(*)>1
David Portas
SQL Server MVP
--

---

|||On Fri, 16 Dec 2005 12:16:57 -0600, rlassiter wrote:

>This is great to show what is duplicated and by adding changing Select
>to count(*) I was able to see how many times it was duplicated. Are you
>able to take this 1 step further and actually return all duplicated and
>complete records? EG. John,Smith is duplicated 3 times but the City is
>different in each case. Can you return the 3 first,last,city records?
>Robert Lassiter
Hi Robert,
Here's one method:
SELECT a.firstname, a.lastname, a.city
FROM YourTable AS a
WHERE (SELECT COUNT(*)
FROM YourTable AS b
WHERE b.firstname = a.firstname
AND b.lastname = a.lastname) > 1
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)