On Tue, 2005-06-28 at 15:45 -0400, Ann W. Harrison wrote:

> William L. Thomson Jr. wrote:
> > I have two tables that I need to query, ... linked via foreign keys to
> > one main table. Table names are A,B,C with the main one being A.
> >
> > Now when data is put into table B it can also go into table C as well. I
> > basically need to get the differences between tables B and C in two
> > queries. Table B having unique data, and table C having that same data,
> > and more.
> >
> > I need one query that returns all records in table B.
>
> select <field list> from B
>
> I'm sure I've missed something but that's the usual way of asking for
> all records from a table.

Yes, I am in idiot. I was not really asking how to do that. I knew how
to do that and more. It was the sub-select returning differences of two
tables I was asking if possible and example how. However I figured it
out before first reply. ;)

> > Then another query
> > that returns only the records in C, that are not in B.
>
> select <field list> from c
> where not exists (select 1 from b where b.field1 = c.field1
> and b.field2 = c.field2 ...)
>
>
> What have I missed?

Only the space in my head were at times a brain should be :)

Although I am curious as to the difference between WHERE NOT EXISTS and
WHERE <field> NOT IN? Is one better than the other, more efficient etc?


--
Sincerely,
William L. Thomson Jr.
Support Group
Obsidian-Studios, Inc.
http://www.obsidian-studios.com