473,836 Members | 2,202 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL syntax - "NOT IN"

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
Jul 20 '05 #1
3 58174
"B. Harwood" <bo**********@y ahoo.ca> wrote in message
news:d0******** *************** ***@posting.goo gle.com...
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


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
Jul 20 '05 #2
>> 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)
Jul 20 '05 #3
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" <ja*@acm.org> wrote in message news:<rd******* *************** @twister.nyc.rr .com>...
"B. Harwood" <bo**********@y ahoo.ca> wrote in message
news:d0******** *************** ***@posting.goo gle.com...
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


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

Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
3211
by: kaeli | last post by:
All, I've been unable to find out if javascript supports for (var e in obj) type of looping syntax. Does it? If so, is this for DOM browsers only? TIA! --
32
3410
by: Will Hartung | last post by:
Can someone clarify that multiple classes in the "class" attribute are ok and "legal" and not some fluke? So, I can do: ..pink {color: pink} ..bold {font-weight: bold} ..medium {font-size: 12pt} <p class="bold pink medium">bold pink medium sized text</p>
2
2067
by: mantas44 | last post by:
Hello all, I have two tables tab_a id(int) tab_b id(int)
4
29358
by: Jacinle Young | last post by:
Hi all, I have written the SQL but it doesn't work db2 "select acode from t1 where (acode) not in (select eid from t2 where iid ='0') and iid ='0'" select nothing but the following works
5
3434
by: Larry R Harrison Jr | last post by:
I use the Lebans calendar control and love it; the only thing is that it commonly starts out at the year 1899 rather than the current year. Is there a way to specifically tell it to start at the current date instead? Note: it does this fine if I'm using it in the context of a form's text box, but I'm trying to use it on a module which is called, whereby the date chosen is the date plugged into a variable. Everytime after I reset the...
1
1571
by: Jean Stax | last post by:
Hi ! A couple of pretty basic questions: Value types: As far as I understand, when I create value type without "new" syntax the object is considered as unutilized. Consequently, I have to initialize its member variables manually; otherwise I would get an exception while accessing them.
3
2124
by: Branco Medeiros | last post by:
Hi all, Using SQL Server 2000, SP4. I have a table of street names (Rua) whose ids (cod_rua) are foreign keys into a consumer table (Consumidor). It turns out that the "Rua" table has many unused records which I'd like to wipe out. For instance, there are some 2800 unused records in the "Rua" table, and only some 200 records actually being used by the "Consumidor" table (which, itself, has some 5000 records).
3
2617
by: ChildProgrammer | last post by:
I am trying to upload data into an Access db from vb.net. The data was parsed out from text files. Everything seems to be working fine up till the .execute point. The debugger insists there is a syntax error in my statement, but when I check my insert into statement, I get what I expect. What am I doing wrong? Here is my code.: Dim str As String Dim s As ADODB.Connection = New ADODB.Connection Dim cmd As OleDbCommand s.ConnectionString =...
4
3902
by: fran7 | last post by:
Hi, from help in the javascript forum I found the error in some code but need help. This bit of code works perfectly, trouble is I am writing it to a javascript function so the height needs to be in &quot;&quot; instead of "" otherwise I get an error message. Can anyone suggest how to write it so that it writes &quot; instead of "". I have tried all combinations of adding &quot; to the code but as soon as I think I am there I get throw out again. if...
6
1766
by: grbgooglefan | last post by:
I am creating functions, the return result of which I am using to make decisions in combined expressions. In some expressions, I would like to inverse the return result of function. E.g. function contains(source,search) will return true if "search" string is found in source string. I want to make reverse of this by putting it as: if ( ! contains(s1,s2) ): return 1
0
9810
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
10527
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10575
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10241
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6975
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5812
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4443
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4001
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3102
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.