Go Back  DVD Talk Forum > General Discussions > Tech Talk
Reload this Page >

MS Access Question

Tech Talk Discuss PC Hardware, Software, Internet and Other Technology

MS Access Question

Old 02-25-04, 02:13 PM
  #1  
DVD Talk Legend
Thread Starter
 
Join Date: Oct 1999
Location: Plano, TX
Posts: 23,226
Likes: 0
Received 0 Likes on 0 Posts
MS Access Question

Does anybody know how to join 2 tables where data in one colum is NOT in the other?

Example

Table one has 1, 2, 3, 4, 5
Table two has 1, 3, 5

I want the join to pull up 2, 4

Thanks.

Last edited by PixyJunket; 02-25-04 at 02:38 PM.
Old 02-25-04, 02:23 PM
  #2  
X
Administrator
 
X's Avatar
 
Join Date: Oct 1987
Location: AA-
Posts: 10,948
Received 48 Likes on 39 Posts
2 is in both tables. Did you mean 1?
Old 02-25-04, 02:24 PM
  #3  
Moderator
 
Groucho's Avatar
 
Join Date: Mar 2000
Location: Salt Lake City, Utah
Posts: 71,383
Received 116 Likes on 78 Posts
Originally posted by X
2 is in both tables. Did you mean 1?
Well, if he knew what the results were going to be, he wouldn't have to put it into Access!
Old 02-25-04, 02:38 PM
  #4  
DVD Talk Legend
Thread Starter
 
Join Date: Oct 1999
Location: Plano, TX
Posts: 23,226
Likes: 0
Received 0 Likes on 0 Posts
Oops.. my bad.. I meant for table two to be 1, 3, 5.. (edits)
Old 02-25-04, 03:18 PM
  #5  
DVD Talk Legend
 
Join Date: Oct 1999
Location: |-|@><0r [email protected]|)
Posts: 17,214
Likes: 0
Received 0 Likes on 0 Posts
2 and 4 are both in the same table. Why are you joining anything to select out this data?

I can help; just clarify what you're trying to accomplish. ie, what should the result set contain?

- David Stein

Last edited by sfsdfd; 02-25-04 at 03:23 PM.
Old 02-25-04, 03:22 PM
  #6  
DVD Talk Legend
 
Join Date: Oct 1999
Location: |-|@><0r [email protected]|)
Posts: 17,214
Likes: 0
Received 0 Likes on 0 Posts
The way a join works, btw: the DBMS looks at a particular attribute/column (or set of attributes/columns) in each table and compares them. For each matching set of tuples (records), the result set will contain one tuple with all of the attributes from each matching tuple in each table.

Example:

Table1 has columns A, B, C, D, and E.

Table2 has columns F, G, H, I, and J.

You do a join of the two tables on columns B and G (SELECT * FROM TABLE1 JOIN TABLE2 AT B=G). The DBMS will first create a new table with all ten columns, A through J. The DBMS will then compare each tuple of Table1 against Table2, and create a tuple in Table3 using values A-E of Table1 and values F-J of Table2.

- David Stein
Old 02-25-04, 03:24 PM
  #7  
DVD Talk Legend
Thread Starter
 
Join Date: Oct 1999
Location: Plano, TX
Posts: 23,226
Likes: 0
Received 0 Likes on 0 Posts
In my above example.. I'm trying to anything in table one that is NOT in table two.

You can do a join that will pull up anything from table one that IS in table two, but I'll be damed if I can figure it out the other way around.

Access 2002 I think.
Old 02-25-04, 03:32 PM
  #8  
X
Administrator
 
X's Avatar
 
Join Date: Oct 1987
Location: AA-
Posts: 10,948
Received 48 Likes on 39 Posts
SELECT Table1.col1, Table2.col1
FROM Table1 LEFT JOIN Table2 ON Table1.col1 = Table2.col1
WHERE ((Table2.col1) Is Null);
Old 02-25-04, 03:37 PM
  #9  
DVD Talk Legend
 
Join Date: Oct 1999
Location: |-|@><0r [email protected]|)
Posts: 17,214
Likes: 0
Received 0 Likes on 0 Posts
Heh... that's the long way around.

Try this:

SELECT * FROM Table1 WHERE NOT EXIST (SELECT * FROM Table2 WHERE Table1.column1 = Table2.column2);

You may also be able to do a set difference: SELECT * FROM TABLE1 - TABLE2;

- David Stein
Old 02-25-04, 04:02 PM
  #10  
X
Administrator
 
X's Avatar
 
Join Date: Oct 1987
Location: AA-
Posts: 10,948
Received 48 Likes on 39 Posts
Actually my way is more efficient, but it doesn't really matter unless you have large datasets.
Old 02-25-04, 04:13 PM
  #11  
DVD Talk Legend
Thread Starter
 
Join Date: Oct 1999
Location: Plano, TX
Posts: 23,226
Likes: 0
Received 0 Likes on 0 Posts
I'll try those out when I get the chance. Thanks.
Old 02-25-04, 04:22 PM
  #12  
DVD Talk Legend
Thread Starter
 
Join Date: Oct 1999
Location: Plano, TX
Posts: 23,226
Likes: 0
Received 0 Likes on 0 Posts
It works! The first suggestion by X.
Old 02-25-04, 05:15 PM
  #13  
DVD Talk Legend
 
Join Date: Oct 1999
Location: |-|@><0r [email protected]|)
Posts: 17,214
Likes: 0
Received 0 Likes on 0 Posts
Originally posted by X
Actually my way is more efficient, but it doesn't really matter unless you have large datasets.
I have two comments about this:

1) It may be practically more efficient since you have one select instead of two, but it's harder to read - your intentions aren't as clear.

2) It might actually provide an incorrect result. Consider what happens if any records in Table2 have a null for Column1. In addition to the tuples you really want, you also get a Cartesian product set between every Table1 record with null in Column1, and each Table2 record with null in Column1.

Example:

Table1 contains tuples A, B, C, D, E.

Table2 contains tuples F, G, H, I, J.

Tuples F and G match Table1 records A and B respectively, but the other six tuples have NULL in Column1.

What PixyJunket wants is simply three tuples: C, D, and E. But instead, he gets the following nine tuples: CH, CI, CJ, DH, DI, DJ, EH, EI, and EJ.

Reason: Your SQL command first does the left join, with the following result: AF, BG, CH, CI, CJ, DH, DI, DJ, EH, EI, EJ. Then it throws out all tuples where the result set column called Table2.Column1 is not null, which is AF and BG. But it returns all of the rest.

- David Stein

Last edited by sfsdfd; 02-26-04 at 01:38 PM.
Old 02-25-04, 10:53 PM
  #14  
DVD Talk Gold Edition
 
Join Date: Dec 1999
Posts: 2,708
Likes: 0
Received 0 Likes on 0 Posts
OR

SELECT table1.yourcolumn
FROM table1
WHERE table1.yourcolumn NOT IN (SELECT table2.yourcolumn FROM table2)
Old 02-26-04, 01:39 PM
  #15  
DVD Talk Legend
 
Join Date: Oct 1999
Location: |-|@><0r [email protected]|)
Posts: 17,214
Likes: 0
Received 0 Likes on 0 Posts
Originally posted by Lampei
SELECT table1.yourcolumn
FROM table1
WHERE table1.yourcolumn NOT IN (SELECT table2.yourcolumn FROM table2)
That's pretty elegant.

- David Stein
Old 02-26-04, 05:46 PM
  #16  
DVD Talk Gold Edition
 
Join Date: Dec 1999
Posts: 2,708
Likes: 0
Received 0 Likes on 0 Posts
I try I've been looking at SQL statements *way* too much lately
Old 02-26-04, 09:50 PM
  #17  
DVD Talk Legend
 
Join Date: Oct 1999
Location: |-|@><0r [email protected]|)
Posts: 17,214
Likes: 0
Received 0 Likes on 0 Posts
Originally posted by Lampei
I try I've been looking at SQL statements *way* too much lately
Yeah, me too. I'm taking a databases course as part of my masters' degree (the first in a three-course sequence.) I'm also studying Microsoft's take on databases (SQL server, ADO.net) in the course of preparing for Microsoft certified professional exams. So the past two months have been a huge brain-cram of SQL, ER diagramming, the relational model, normal forms...

- David Stein

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 On
HTML code is On
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Thread Tools
Search this Thread

Archive - Advertising - Cookie Policy - Privacy Statement - Terms of Service - Do Not Sell My Personal Information -

Copyright 2021 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.