Re: ???? SQL question ????

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/01/13
Message-ID: <34bbdd8c.3955227@www.sigov.si>#1/1

On Tue, 13 Jan 1998 13:37:38 +0100, ilepper_at_fac.fbk.eur.nl (ilepper) wrote:

>Ok here's a nice SQL problem, who can solve this one?
>
>I have got 2 tables
>
>Table 1 :
>prim. key : Id1, Id2, Id3
>
>Table 2 :
>prim. key : Id
>for. key : Table1_id1, Table1_id2, Table1_id3
>
>I want to do the following : I would like all records from table 1 where
>there's no record for it in table 2.
>
>Usualy it would look something like this :
>select * from table 1 where id not in (select id from table 2)
>but now table one has more attributes forming the prim. key.
>
>How can it be done, anyone?

Whenever possible, use EXISTS operator instead of IN - it is more efficient. For your example, the following will do:

SELECT * FROM table1 t1 WHERE NOT EXISTS

   (SELECT 1 FROM table2 t2

    WHERE t2.table1_id1 = t1.id1
      AND t2.table1_id2 = t1.id2
      AND t2.table1_id3 = t1.id3)

>Thanks anyway for trying,
>by the way, I am using Orcale version 7, and Delphi 3 to excute the SQL
>query.
>
>Ido de Lepper
>ilepper_at_fac.fbk.eur.nl

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Tue Jan 13 1998 - 00:00:00 CST