Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    78

    Unanswered: Selecting different records from two tables

    Hi All,
    I have two tables table A and table B and they are linked together on an IDNo. Table A is the main table and Table B is the subsidiary. Now my question is that I want to query all the records from Table A which are not present in Table B. So for example Table A has 3 records with IDNo x1, x2 and x3 and table B has two records with IDNo x1 and x2, so I want to write a query which will extract only record x3 from Table A. Usually we select common records from both tables, but my case here is different. I tried but cannot think of a way to do it. Can anybody suggest a solution to this?

    Cheers:
    Prathmesh

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can use either a NOT EXISTS sub-query, or a LEFT JOIN and test for null values in the child FK.

    -PatP

  3. #3
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    100

    Re: Selecting different records from two tables

    Originally posted by Prathmesh
    Hi All,
    I have two tables table A and table B and they are linked together on an IDNo. Table A is the main table and Table B is the subsidiary. Now my question is that I want to query all the records from Table A which are not present in Table B. So for example Table A has 3 records with IDNo x1, x2 and x3 and table B has two records with IDNo x1 and x2, so I want to write a query which will extract only record x3 from Table A. Usually we select common records from both tables, but my case here is different. I tried but cannot think of a way to do it. Can anybody suggest a solution to this?

    Cheers:
    Prathmesh
    I think you can try with 2 SQLs. The first will extract the IDNo of Table B. Then the second one is a SELECT * FROM TableA WHERE TableA.IDNo NOT IN (SELECT IDNo FROM TableB)

    Or something like that, do you understand me?

  4. #4
    Join Date
    Oct 2003
    Posts
    78
    Hello.
    As suggested by Pat, I tried out the null checking and it worked. Below is the query I used.

    SELECT T1.ID, T1.Name
    FROM table2 AS T2 RIGHT JOIN table1 AS T1 ON T2.ID = T1.ID
    WHERE (T2.ID) Is Null);

    This is not the exact query that I used but an example of the query I used with different table names.

    Thanks all for the help and efforts.

    Cheers.
    Prathmesh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •