Hi,

Hoping someone can assist me with a query I have.

I have two tables, both of which have at least one field of the same
name, type, etc, etc. This field I will call SKEY, for the purposes of
this post. SKEY is a CHAR field, 4 chars in length.

One of the tables, we'll say Table1, has the complete list of
available SKEY's. Table2, on the other hand, contains only some of the
available SKEY's.

I want to be able to find out which SKEY's are missing from Table2;
ie. which SKEY's are in Table1, but not in Table2.

I have tried several different queries, but generally end up in a bit
of a mess, more often returning tens of thousands of results, when in
fact there are only a couple of hundred records in total.

My pseudo-code would be something like:

SELECT SKEY FROM TABLE1 WHERE TABLE1.SKEY NOT LIKE TABLE2.SKEY

The basic query which I tried is:

SELECT TABLE1.SKEY FROM TABLE1 JOIN TABLE2 WHERE TABLE1.SKEY NOT LIKE
TABLE2.SKEY

I also tried a few variations, with DISTINCT, etc, etc ... but I never
end up with anything close to what I need.

So, I'm sure I'm missing some basic knowledge about such a query. I
realise I'm getting thousands of records returned because it's testing
every single record in Table1 against every single record in
Table2 ... but I don't get how to do the type of query I want.

Any assistance much appreciated!

Superman183

superman183 wrote:
If SKEY in TABLE2 cannot be null.

SELECT TABLE1.SKEY FROM TABLE1
LEFT JOIN TABLE2 ON TABLE2.SKEY = TABLE1.SKEY
WHERE TABLE2,SKEY IS NULL;
--
Rik Wasmus
[SPAM]
Now looking for some smaller projects to work on to fund a bigger one
with delayed pay. If interested, mail rik at rwasmus.nl
[/SPAM]

On 9 May, 11:54, Rik Wasmus <luiheidsgoe... (AT) hotmail (DOT) com> wrote:
LEFT JOIN TABLE2 ON TABLE2.SKEY = TABLE1.SKEY
WHERE TABLE2.SKEY IS NULL;

(replaced comma with full stop)

Captain Paralytic wrote:
Hmmm, What excuse will I use now? National holidays have been over for a
few days is wat sent sober & at a normal hour...

I know: "Damn those laptop keyboards, give a me a real one!"

Thanks for the correction
--
Rik Wasmus
[SPAM]
Now looking for some smaller projects to work on to fund a bigger one
with delayed pay. If interested, mail rik at rwasmus.nl
[/SPAM]

On 9 May, 13:36, Rik Wasmus <luiheidsgoe... (AT) hotmail (DOT) com> wrote:

Captain Paralytic wrote:
I like it how I managed to get a completely bogus sentence:'..is wat
sent..' while claiming to be sober .

s/is wat/and was/
--
Rik Wasmus
[SPAM]
Now looking for some smaller projects to work on to fund a bigger one
with delayed pay. If interested, mail rik at rwasmus.nl
[/SPAM]

Rik Wasmus wrote:
Rik, you haven't been sober a day in your life! <gd&r>

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On Fri, 09 May 2008 04:44:55 -0700, superman183 wrote:


Have you tried this code?

select skey from table1, table2 where table1.skey not in table2.skey

Or, pulling your complete second table as a sub-query (I think this may
be needed; it's been a while):

select a.skey from table1 a, (select distinct skey from table2) b where
a.skey not in b

Luck

Many thanks to all respondents, much appreciated!

Superman183

On 9 May, 23:31, user <u... (AT) 127 (DOT) 0.0.1> wrote:
Luck, good name

A lot of luck will be needed to get your first proposal to work.

As to your second one, Rik's solution does it far more efficiently.

As a general rule, use JOINs (or LEFT JOINs) wherever possible and
avoid sub-selects such as yours.

Also, always code explicit JOINs rather than implicit comma joins.