sql - problem with join?
sql - problem with join?
sql - problem with join?
sql - problem with join?
sql - problem with join?
sql - problem with join? sql - problem with join? sql - problem with join? sql - problem with join? sql - problem with join? sql - problem with join? sql - problem with join? sql - problem with join?
sql - problem with join? sql - problem with join?
sql - problem with join?
Go Back  Xtreme Visual Basic Talk > > > sql - problem with join?


Reply
 
Thread Tools Display Modes
  #1  
Old 09-21-2006, 03:05 PM
Beckett Beckett is offline
Regular
 
Join Date: Apr 2005
Location: Frisco, TX
Posts: 83
Default sql - problem with join?


I have 2 tables with the same Products field. I want to select all products from table1 that are NOT in table2.
table1:
products
product1
product2
product3
product4

table2 has:
products
product3
product5

so I would like my results to be product1, product2 and product4. I can't seem to get the right sql select statement -- I am not used to selecting from more than one table:

select table1.products
from table1, table2
where table1.products <> table2.products

that doesn't work -- I get everything from table1 when I do that. I only wanted the products in table1 that are NOT in table2.

thanks in advance for any help
Reply With Quote
  #2  
Old 09-21-2006, 03:28 PM
00100b's Avatar
00100bsql - problem with join? 00100b is offline
Martian In Disguise

Retired Moderator
* Guru *
 
Join Date: May 2003
Location: Minneapolis, MN
Posts: 9,566
Default

Use a LEFT OUTER JOIN and evaluate where table2.products IS NULL
Code:
SELECT table1.products
FROM table1
LEFT OUTER JOIN table2 ON table2.products = table1.products
WHERE table2.products IS NULL
__________________
The only stupid question is the one that goes un-asked.
Reply With Quote
  #3  
Old 09-21-2006, 03:29 PM
reboot's Avatar
rebootsql - problem with join? reboot is offline
Keeper of foo

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Graceland
Posts: 15,614
Default

select products from table1 where products not in (select products from table2)
__________________
~ Quod non mortiferum, fortiorem me facit ~

Avatar by lebb
Reply With Quote
  #4  
Old 09-21-2006, 03:43 PM
Beckett Beckett is offline
Regular
 
Join Date: Apr 2005
Location: Frisco, TX
Posts: 83
Default

thanks so much - both solutions worked perfectly.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
sql - problem with join?
sql - problem with join?
sql - problem with join? sql - problem with join?
sql - problem with join?
sql - problem with join?
sql - problem with join? sql - problem with join? sql - problem with join? sql - problem with join? sql - problem with join? sql - problem with join? sql - problem with join?
sql - problem with join?
sql - problem with join?
 
sql - problem with join?
sql - problem with join?
 
-->