Re: Question on a select - Mailing list pgsql-general

There are several ways. I am making the simplifying assumption that name,
type and dir cannot be NULL in either table. If they are the query is a
little more complicated.

The following are a couple of many techniques.

SELECT a.a_name
             , a.a_type
             , a.a_dir
     FROM a_table a
  WHERE NOT EXISTS
              ( SELECT NULL
                     FROM b_table b
                  WHERE b.b_name      = a.a_name
                        AND b.b_type        = a.a_type
                        AND b.b_dir          = a.a_dir
              )

SELECT a.a_name
             ,  a.a_type
             ,  a.a_dir
    FROM a_table      a
                 LEFT JOIN b_table b
                        ON  a.a_table    = b.b_table
                      AND a.a_type     = b.b_type
                      AND a.a_dir       = b.b_type
 WHERE b.b_table IS NULL                           // assumes that b.b_table
is a not null column.

Let's say that dir could be null and dir is a string, then (assuming that
dir can never be 'xyz') you could say something like

COALESCE(a.a_dir,'xyz')  = COALESCE(b.b_dir,'xyz')

Since NULL never equal NULL, if you want NULL in one table to match a NULL
in another table, you need to change it to something not NULL. However this
depends on what you want in your application.

Queries like this are used often to check the integrity of your data.
Examples of this are 1) What orders don't have order items?  2) What books
have no authors? etc.


----- Original Message -----
From: "Madison Kelly" <linux@alteeve.com>
To: "PgSQL General List" <pgsql-general@postgresql.org>
Sent: Saturday, January 01, 2005 7:32 PM
Subject: [GENERAL] Question on a select


> Hi all,
>
>   This is my first post here so please let me know if I miss any list
> guidelines. :)
>
>   I was hoping to get some help, advice or pointers to an answer for a
> somewhat odd (to me at least) SELECT. What I am trying to do is select
> that values from one table where matching values do not exist in another
> table.
>
> For example:
>
>   Let's say 'table_a' has the columns 'a_name, a_type, a_dir, a_<others>'
> and 'table_b' has the columns 'b_name, b_type, b_dir, b_<others>' where
> 'others' are columns unique to each table. What I need to do is select all
> the values in 'a_name, a_type, a_dir' from 'table_a' where there is no
> matching entries in "table_b's" 'b_name, b_type, b_dir'.
>
>   I know I could do something like:
>
> SELECT a_name, a_type, a_dir FROM table_a;
>
>   and then loop through all the returned values and for each do a matching
> select from 'table_b' and use my program to catch the ones not in
> 'table_b'. This is not very efficient though and I will be searching
> through tables that could have several hundred thousand entries so the
> inefficiency would be amplified. Is there some way to use a join or
> something similar to do this?
>
>   Thank you all!
>
> Madison
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org
>