SQL syntax - "NOT IN"

[B. Harwood]

This is probably a simple question, but I'm stumped. I cannot figure
out a way to do the following. For example, say I have two tables -
one of used toys and one of broken toys. Say I wanted to see a list
of all the distinct used toys that were not in the broken toys list.
The two tables are as follows:

USED TOYS

TOY COLOUR

Red Truck
Blue Truck
Green Ball
Blue Ball
Red Bike




BROKEN TOYS

TOY COLOUR

Pink Doll
Red Truck
Blue Ball
Orange Bike


How do I show the unique combinations of the two fields (ie. just the
Red Truck and Blue Ball)?

Brenda

[John Gilson]

Re: SQL syntax - "NOT IN"

"B. Harwood" <boriscatbobo@y ahoo.ca> wrote in message
news:d0ead08b.0 312160957.73c88 e6b@posting.goo gle.com...[color=blue]
> This is probably a simple question, but I'm stumped. I cannot figure
> out a way to do the following. For example, say I have two tables -
> one of used toys and one of broken toys. Say I wanted to see a list
> of all the distinct used toys that were not in the broken toys list.
> The two tables are as follows:
>
> USED TOYS
>
> TOY COLOUR
>
> Red Truck
> Blue Truck
> Green Ball
> Blue Ball
> Red Bike
>
>
>
>
> BROKEN TOYS
>
> TOY COLOUR
>
> Pink Doll
> Red Truck
> Blue Ball
> Orange Bike
>
>
> How do I show the unique combinations of the two fields (ie. just the
> Red Truck and Blue Ball)?
>
> Brenda[/color]

You should use a NOT EXISTS subquery, e.g., all the used toys that are
not broken toys would be

SELECT *
FROM UsedToys AS U
WHERE NOT EXISTS (SELECT *
FROM BrokenToys AS B
WHERE B.color = U.color AND
B.toy = U.toy)

For all the used toys that are broken toys, change the NOT EXISTS to
EXISTS.

Regards,
jag

[--CELKO--]

Re: SQL syntax - &quot;NOT IN&quot;

>> I have two tables - one of used toys and one of broken toys. Say I
wanted to see a list of all the distinct used toys that were not in
the broken toys list. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Your column names are flipped in your pesonal
pseudo-code diagram!

Actually, this is a bad design; you need one table of toys with a
status code for (used, broken, new, etc.).

And the spec is bad; your example shows the only toys that are both
used and broken.

SELECT DISTINCT U1.*
FROM Used AS U1
WHERE NOT EXISTS
(SELECT *
FROM Broken AS B1
WHERE B1.color = U1.color
AND B1.toy = U1.toy)

[B. Harwood]

Re: SQL syntax - &quot;NOT IN&quot;

John

Thank you so much for your response - it's exactly what I needed.
Sorry about the poor format and content of my question yesterday. I
was in a big hurry to get to a meeting and sent my poorly-worded
question without even reading it over.

Brenda




"John Gilson" <jag@acm.org> wrote in message news:<rdKDb.218 209$Gq.28369886 @twister.nyc.rr .com>...[color=blue]
> "B. Harwood" <boriscatbobo@y ahoo.ca> wrote in message
> news:d0ead08b.0 312160957.73c88 e6b@posting.goo gle.com...[color=green]
> > This is probably a simple question, but I'm stumped. I cannot figure
> > out a way to do the following. For example, say I have two tables -
> > one of used toys and one of broken toys. Say I wanted to see a list
> > of all the distinct used toys that were not in the broken toys list.
> > The two tables are as follows:
> >
> > USED TOYS
> >
> > TOY COLOUR
> >
> > Red Truck
> > Blue Truck
> > Green Ball
> > Blue Ball
> > Red Bike
> >
> >
> >
> >
> > BROKEN TOYS
> >
> > TOY COLOUR
> >
> > Pink Doll
> > Red Truck
> > Blue Ball
> > Orange Bike
> >
> >
> > How do I show the unique combinations of the two fields (ie. just the
> > Red Truck and Blue Ball)?
> >
> > Brenda[/color]
>
> You should use a NOT EXISTS subquery, e.g., all the used toys that are
> not broken toys would be
>
> SELECT *
> FROM UsedToys AS U
> WHERE NOT EXISTS (SELECT *
> FROM BrokenToys AS B
> WHERE B.color = U.color AND
> B.toy = U.toy)
>
> For all the used toys that are broken toys, change the NOT EXISTS to
> EXISTS.
>
> Regards,
> jag[/color]