SQL Problem

#1

I guess it's my inexperience but I cannot figure out how to write SQL
for the following case:

I have two tables. Table1 has the numbers 1 through 100 (no
duplicates). The Table2 has "Some" of the numbers 1 through 100 and
there may be duplicates in Table2. I would like to SELECT a list of
the numbers missing from Table2.

And to carry it one step further. I want to use the numbers missing
from Table2 to SELECT the rows from TABLE 1 with these numbers. This
is the final result.

Suggestions??

Thank you...

Bruce

#2

bruce wrote:

Quote:

I guess it's my inexperience but I cannot figure out how to write SQL
for the following case:

I have two tables. Table1 has the numbers 1 through 100 (no
duplicates). The Table2 has "Some" of the numbers 1 through 100 and
there may be duplicates in Table2. I would like to SELECT a list of
the numbers missing from Table2.

My sql is really crap, but that has to be something like

select num from table1 where not in (select distinct num from table 2)..

Quote:

And to carry it one step further. I want to use the numbers missing
from Table2 to SELECT the rows from TABLE 1 with these numbers. This
is the final result.

select * from table1 where num not in (select distinct num as num2 from

table2)

??

Quote:

Suggestions??

Thank you...

Bruce

#3

bruceaj (AT) bellsouth (DOT) net says...

Quote:

I guess it's my inexperience but I cannot figure out how to write SQL
for the following case:

I have two tables. Table1 has the numbers 1 through 100 (no
duplicates). The Table2 has "Some" of the numbers 1 through 100 and
there may be duplicates in Table2. I would like to SELECT a list of
the numbers missing from Table2.

And to carry it one step further. I want to use the numbers missing
from Table2 to SELECT the rows from TABLE 1 with these numbers. This
is the final result.

RTFM on the use of the MINUS function to get a list of numbers that are
in Table1 but not in Table2.

RTFM on the use of IN (SUBSELECT) using the result of the first set.

GM

#4

On May 5, 6:54*pm, The Natural Philosopher <t... (AT) invalid (DOT) invalid>
wrote:

Quote:

bruce wrote:
I guess it's my inexperience but I cannot figure out how to write SQL
for the following case:

I have two tables. Table1 has the numbers 1 through 100 (no
duplicates). The Table2 has "Some" of the numbers 1 through 100 and
there may be duplicates in Table2. I would like to SELECT a list of
the numbers missing from Table2.

My sql is really crap, but that has to be something like

select num from table1 where not in (select distinct num from table 2)..

And to carry it one step further. I want to use the numbers missing
from Table2 to SELECT the rows from TABLE 1 with these numbers. This
is the final result.

select * from table1 where num not in (select distinct num as num2 from
table2)

??

Suggestions??

Thank you...

Bruce

Thanks for the response. Works like a charm.

Thanks....

#5

On May 5, 7:02*pm, Geoff Muldoon <geoff.muld... (AT) trap (DOT) gmail.com> wrote:

Quote:

bruc... (AT) bellsouth (DOT) net says...

I guess it's my inexperience but I cannot figure out how to write SQL
for the following case:

I have two tables. Table1 has the numbers 1 through 100 (no
duplicates). The Table2 has "Some" of the numbers 1 through 100 and
there may be duplicates in Table2. I would like to SELECT a list of
the numbers missing from Table2.

And to carry it one step further. I want to use the numbers missing
from Table2 to SELECT the rows from TABLE 1 with these numbers. This
is the final result.

RTFM on the use of the MINUS function to get a list of numbers that are
in Table1 but not in Table2.

RTFM on the use of IN (SUBSELECT) using the result of the first set.

GM

Thanks for the response..

What is RTFM??

Bruce

#6

On May 5, 7:36*pm, bruce <bruc... (AT) bellsouth (DOT) net> wrote:

Quote:

On May 5, 7:02*pm, Geoff Muldoon <geoff.muld... (AT) trap (DOT) gmail.com> wrote:

bruc... (AT) bellsouth (DOT) net says...

I guess it's my inexperience but I cannot figure out how to write SQL
for the following case:

I have two tables. Table1 has the numbers 1 through 100 (no
duplicates). The Table2 has "Some" of the numbers 1 through 100 and
there may be duplicates in Table2. I would like to SELECT a list of
the numbers missing from Table2.

And to carry it one step further. I want to use the numbers missing
from Table2 to SELECT the rows from TABLE 1 with these numbers. This
is the final result.

RTFM on the use of the MINUS function to get a list of numbers that are
in Table1 but not in Table2.

RTFM on the use of IN (SUBSELECT) using the result of the first set.

GM

Thanks for the response..

What is RTFM??

Bruce- Hide quoted text -

- Show quoted text -

Really?? Read The Fine Manual (or F......)

#7

In article <739063bd-c34c-4715-9ceb-e25890483176@
28g2000yqu.googlegroups.com>, bruceaj (AT) bellsouth (DOT) net says...

Quote:

On May 5, 7:02*pm, Geoff Muldoon <geoff.muld... (AT) trap (DOT) gmail.com> wrote:
bruc... (AT) bellsouth (DOT) net says...

I guess it's my inexperience but I cannot figure out how to write SQL
for the following case:

I have two tables. Table1 has the numbers 1 through 100 (no
duplicates). The Table2 has "Some" of the numbers 1 through 100 and
there may be duplicates in Table2. I would like to SELECT a list of
the numbers missing from Table2.

And to carry it one step further. I want to use the numbers missing
from Table2 to SELECT the rows from TABLE 1 with these numbers. This
is the final result.

RTFM on the use of the MINUS function to get a list of numbers that are
in Table1 but not in Table2.

RTFM on the use of IN (SUBSELECT) using the result of the first set.

GM

Thanks for the response..

What is RTFM??

Read
The
F
Manual

:-)

I note that one of the other replies gave you code that achieved what
you wanted to do. Just some advice - be aware that there is more than
one way to skin a cat. Sometime two methods will functionally work the
same, but performance might fluctuate wildly depending on record counts,
indexing options, etc.

My alternative code:

select * from Table1
where num in
(select num from Table1
minus
select num from Table2);

Uses IN rather than NOT IN and MINUS rather than DISTINCT. Sometimes
this will work faster, sometimes it's a sledgehammer on a thumbtack.

Additionally, in some cases (but probably not this one) it's better to
use NOT EXISTS rather than NOT IN. Google those and PERFORMANCE for an
explanation on why.

GM

#8

On 5/5/2011 4:11 PM, bruce wrote:

Quote:

I guess it's my inexperience but I cannot figure out how to write SQL
for the following case:

I have two tables. Table1 has the numbers 1 through 100 (no
duplicates). The Table2 has "Some" of the numbers 1 through 100 and
there may be duplicates in Table2. I would like to SELECT a list of
the numbers missing from Table2.

And to carry it one step further. I want to use the numbers missing
from Table2 to SELECT the rows from TABLE 1 with these numbers. This
is the final result.

Suggestions??

Thank you...

Bruce

Subselects are very inefficient in MySQL. Rather, something like:

SELECT col1, col2,...
FROM Table1 a
LEFT JOIN Table2 b ON a.col1 = b.colx
WHERE b.colx IS NULL

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

#9

bruce wrote:

Quote:

On May 5, 7:02 pm, Geoff Muldoon <geoff.muld... (AT) trap (DOT) gmail.com> wrote:
bruc... (AT) bellsouth (DOT) net says...

I guess it's my inexperience but I cannot figure out how to write SQL
for the following case:
I have two tables. Table1 has the numbers 1 through 100 (no
duplicates). The Table2 has "Some" of the numbers 1 through 100 and
there may be duplicates in Table2. I would like to SELECT a list of
the numbers missing from Table2.
And to carry it one step further. I want to use the numbers missing
from Table2 to SELECT the rows from TABLE 1 with these numbers. This
is the final result.
RTFM on the use of the MINUS function to get a list of numbers that are
in Table1 but not in Table2.

RTFM on the use of IN (SUBSELECT) using the result of the first set.

GM

Thanks for the response..

What is RTFM??

Read the fucking manual.

> Bruce

#10

Geoff Muldoon wrote:

Quote:

In article <739063bd-c34c-4715-9ceb-e25890483176@
28g2000yqu.googlegroups.com>, bruceaj (AT) bellsouth (DOT) net says...
On May 5, 7:02 pm, Geoff Muldoon <geoff.muld... (AT) trap (DOT) gmail.com> wrote:
bruc... (AT) bellsouth (DOT) net says...

I guess it's my inexperience but I cannot figure out how to write SQL
for the following case:
I have two tables. Table1 has the numbers 1 through 100 (no
duplicates). The Table2 has "Some" of the numbers 1 through 100 and
there may be duplicates in Table2. I would like to SELECT a list of
the numbers missing from Table2.
And to carry it one step further. I want to use the numbers missing
from Table2 to SELECT the rows from TABLE 1 with these numbers. This
is the final result.
RTFM on the use of the MINUS function to get a list of numbers that are
in Table1 but not in Table2.

RTFM on the use of IN (SUBSELECT) using the result of the first set.

GM
Thanks for the response..

What is RTFM??

Read
The
F
Manual

:-)

I note that one of the other replies gave you code that achieved what
you wanted to do. Just some advice - be aware that there is more than
one way to skin a cat. Sometime two methods will functionally work the
same, but performance might fluctuate wildly depending on record counts,
indexing options, etc.

My alternative code:

select * from Table1
where num in
(select num from Table1
minus
select num from Table2);

Uses IN rather than NOT IN and MINUS rather than DISTINCT. Sometimes
this will work faster, sometimes it's a sledgehammer on a thumbtack.

I did issue a health warning with my suggestion, which much to my
surprise seems to have worked..

Quote:

Additionally, in some cases (but probably not this one) it's better to
use NOT EXISTS rather than NOT IN. Google those and PERFORMANCE for an
explanation on why.

Better still..

I've learnt something tonight..

> GM