Re: Question on a select

On Sat, Jan 01, 2005 at 22:32:17 -0500,
Madison Kelly <linux(at)alteeve(dot)com> wrote:
> 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'.

SELECT a_name, a_type, a_dir, a_<others> FROM table_a
WHERE a_name, a_type, a_dir NOT IN (
SELECT b_name, b_type, b_dir FROM table_b)
;

In pre 7.4 versions or if there are NULLs in the key columns for table_b
then you probably want to use NOT EXISTS (with a moodified WHERE clause)
instead on NOT IN.