Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 6th, 2004, 08:49 AM
Registered User
 
Join Date: Jun 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Simple Question - SQL for retrieving different row

I have two tables:

tableA has a field numberA, which has rows:
1
2
4

tableB has a field numberB, which has rows:
1
2
3

When I use SQL:

select numberB
from tableA and tableB
where tableA.numberA <> tableB.numberB

I expect to output 3 only, but actually, I output many numbers. It seems to be that for each row in tableA, it looks for all different numbers in tableB. My question is what is the simple way to output 4 in tableB only?

This may be a trivial question. Many thanks to any suggestion.

 
Old June 6th, 2004, 11:13 PM
Friend of Wrox
 
Join Date: May 2004
Posts: 642
Thanks: 0
Thanked 43 Times in 42 Posts
Default

Does these tables have ant Primary Keys..? If you join with the given structure, you will get many records.

Om Prakash
 
Old June 8th, 2004, 12:42 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 129
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Does this help (put a primary key in both tables and join them one-to-one in the relationships window)?

SELECT DISTINCTROW TableB.NumberB
FROM TableA INNER JOIN TableB ON TableA.TableAID = TableB.TableBID
WHERE ((([numberA]-[NumberB])<>0));


Clive Astley
 
Old June 9th, 2004, 08:40 AM
Registered User
 
Join Date: Jun 2003
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This query will accomplish what you are trying to do:

SELECT TableA.numberA, TableB.numberB
FROM TableA LEFT JOIN TableB ON TableA.numberA = TableB.numberB
WHERE (((TableB.numberB) Is Null));
 
Old June 9th, 2004, 09:35 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 129
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Jack, this doesn't appear to give the result Wanda says he is expecting?

Clive Astley
 
Old June 9th, 2004, 01:17 PM
Registered User
 
Join Date: Jun 2003
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I guess it depends on how we interpret the question.
In start of question there is reference to 3. At end of question, it is how do I get the 4???


In any event,

1)give me the numbers in TableA that are not in TableB

SELECT TableA.numberA, TableB.numberB
FROM TableA LEFT JOIN TableB ON TableA.numberA = TableB.numberB
WHERE (((TableB.numberB) Is Null));
 result -->4

2) give me the numbers in TableB that are not in TableA

 SELECT TableA.numberA, TableB.numberB
FROM TableA RIGHT JOIN TableB ON TableA.numberA = TableB.numberB
WHERE (((TableA.numberA) Is Null));
  result-->3

jack d





Similar Threads
Thread Thread Starter Forum Replies Last Post
Simple Question dpkbahuguna Java Basics 2 May 19th, 2006 12:05 AM
Simple Question - SQL Statement mrideout BOOK: Beginning ASP.NET 1.0 2 January 11th, 2005 09:28 AM
Very Simple SQL Question?? mrideout BOOK: Beginning ASP.NET 1.0 3 September 20th, 2004 08:07 PM
Simple Question dinosaur_uk VB.NET 2002/2003 Basics 1 September 10th, 2004 09:43 AM
Simple SQL Question cjmarek Classic ASP Databases 1 July 10th, 2004 04:11 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.