Re: Help with SQL Syntax

From: <champs_at_cbr2>
Date: 15 Dec 92 19:18:31 GMT
Message-ID: <1992Dec15.191831.1_at_cbr2>

In article <1992Dec4.202332.21512_at_cbfsb.cb.att.com>, gerette_at_cbnewsb.cb.att.com (marianne.g.pittorino) writes:
>
> Let's call the first table FIRST and the second table SECOND. The data in
>
> I need an SQL statement that will select the rows in FIRST that do not
> match any row in SECOND (and vice versa). For example, let's pretend we have
> the following data:
> FIRST SECOND
> ----- ------
> 1111 1111
> 2222 2222
> 3333 4444
> 5555 5555
>
> I need to know that 3333 exists in FIRST but not in SECOND, and that 4444
> exists in SECOND but not in FIRST. I do not need to know this in the same
> SQL statement.
>
> Does anyone know how to do this?
>

After consulting the SQL reference manual, the simplest way (I don't know if it's the most resource efficient way) is as follows:

	SELECT COL FROM FIRST
         MINUS
        SELECT COL FROM SECOND;

This returns all rows from FIRST that are not is second, so just reverse the table names to get the appropriate rows from SECOND.

Bye for now.

---

    /\       Steven Champness
   /  \      Dept Health Housing and Community Services
  /    \     Brisbane Queensland
 /      \     via

/ / \ Canberra Australia
\ / / /
 \/ / / / MACINTOSH SOFTWARE AXIOM : If you need a manual to operate it

  \/ / /                                it wasn't designed properly in the
   \/ /                                 first place !

    \/ Received on Tue Dec 15 1992 - 20:18:31 CET