I'm a beginner with SQL programming, so please bear with me. I'm
having the following issue:

SELECT table1.id FROM table1, table2 WHERE table1.id<>table2.id


For whatever reason this query returns two of each id from table1
except where it equals the id from table2, in which case it only
returns one id.

The id columns contain unique values and I don't understand why it's
returning two of each instead of one row for each id that doesn't
match the IDs from table2. I just want all the IDs that aren't also in
table2 returned.

Any help would be appreciated. Thanks!

rdlowrey@gmail.com wrote:
> I'm a beginner with SQL programming, so please bear with me. I'm
> having the following issue:
>
> SELECT table1.id FROM table1, table2 WHERE table1.id<>table2.id
>
>
> For whatever reason this query returns two of each id from table1
> except where it equals the id from table2, in which case it only
> returns one id.
>
> The id columns contain unique values and I don't understand why it's
> returning two of each instead of one row for each id that doesn't
> match the IDs from table2. I just want all the IDs that aren't also in
> table2 returned.
>
> Any help would be appreciated. Thanks!
>

Do you have two (or three) rows in table2?

What's happening is for each row in table1 it's looking for a
non-matching row in table2. So if both table1 and table2 had (1, 2, 3)
as id's, it would compare:

1!=1 - false
1!=2 - true
1!=3 - true
2!=1 - true
2!=2 - false
2!=3 - true
3!=1 - true
3!=2 - true
3!=3 - false

And print the true values


You could do it with a subselect (Mysql 4 & above), i.e.

SELECT id FROM table1
WHERE id NOT IN (SELECT id FROM table2)

Not too bad for small tables, but MySQL doesn't optimize this very well
and as your tables grow you'll have longer and longer searches.

A better way would be to use a LEFT JOIN to do it. This returns all
values for table1 and matching values for table2. If there is no
matching value in table2, it returns NULL. You can then just pick out
the ones where NULL is returned in table2, i.e.

SELECT id FROM table1
LEFT JOIN table2 ON table1.id = table2.id
WHERE table2.id is NULL

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

Thanks Jerry! I appreciate it!