Re: [GENERAL] Question on a select

[Vincent Hikida]

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" <[EMAIL PROTECTED]>
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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faqs/FAQ.html