CODEWITHSUNDEEP
ms-accessnullms-access-2010
joem
joem

Reputation: 61

Query between two tables to find like records that includes Null values

I've been having a few problem developing a query in MS Access 2010. I have not been able to find a solution on the net as of yet but I feel that I'm really close. Here is what I'm trying to do:

  1. I have two tables that I'm trying to compare and find like items. Table A is called "DONOTDELETE_FPTable". Table B is called "Imported".
  2. I'm comparing the same columns in both tables. IP, QID and PORT.
  3. My goal is to find like records in the Imported table that occur in the DONOTDELETE_FPTable.
  4. I have a Left Join setup from the DONOTDELETE_FPTable to the Import table on all columns mentioned above.

Problem: I have a situation where a record may have a null value in the column PORT in both tables. I need the query to return:

  1. All matching records between the two tables that have the same data in all columns
  2. AND any matching record that has a matching IP, QID and a possible null in the PORT column between the two tables.

Here is the SQL that I have at this point using the "LIKE" criteria. I removed an data where I'm trying to test for the null since I can't get it to work. Thanks for the help ahead of time.

SQL Statement:

SELECT DONOTDELETE_FPExcept.*
FROM
    DONOTDELETE_FPExcept
    LEFT JOIN Imported
    ON
            (DONOTDELETE_FPExcept.Port = Imported.PORT)
        AND (DONOTDELETE_FPExcept.QID = Imported.QID)
        AND (DONOTDELETE_FPExcept.IP = Imported.IP)
WHERE
        (((Imported.IP) Like [DONOTDELETE_FPExcept].[ip])
    AND ((Imported.qid) Like [DONOTDELETE_FPExcept].[QID])
    AND ((Imported.PORT) Like [DONOTDELETE_FPExcept].[Port]));

Here are the is some example data in the table.IP, QID and Port are the columns:

Import Table:

Notes: Record 2 - Blank Port is Null. Record 3 should not match and not appear in query.

DONOTDELETE_FPTable

Expected Results with Query (Matching Records between two tables with Null):

Actually query result (Issue - Null record is missing)

Upvotes: 4

Views: 3649

Answers (2)

joem
joem

Reputation: 61

Here is the solution to my question that I came up with. After testing different scenarios, I found that I got the expected results from my queries. Here is the MS Access 2010 query. Please note that MS Access added the additional punctuation and I believe this was my original issue:

SQL Code as seen in MS Access:

SELECT Imported.ip, Imported.qid, Imported.port

FROM

DONOTDELETE_FPExcept 
LEFT JOIN Imported 
ON 
     (DONOTDELETE_FPExcept.QID = Imported.QID) 
     AND (DONOTDELETE_FPExcept.IP = Imported.IP)

WHERE 
     (((Imported.ip)=DONOTDELETE_FPExcept.IP)       And  
     (Imported.qid)=DONOTDELETE_FPExcept.qid)       And
     ((Imported.port)=DONOTDELETE_FPExcept.Port))   Or 
     (((Imported.port) Is Null)                     And 
     ((DONOTDELETE_FPExcept.port) Is Null));

Let me know your thoughts. Thanks again for the help.

Upvotes: 2

HansUp
HansUp

Reputation: 97131

With the sample data you added to the question, I get the results you asked for with this query in Access 2007:

SELECT
    d.Record_id AS d_id,
    d.IP,
    d.QID,
    d.Port,
    i.Record_id AS i_id
FROM
    DONOTDELETE_FPExcept AS d
    LEFT JOIN Imported AS i
    ON (d.QID = i.QID) AND (d.IP = i.IP)
WHERE
        d.Port = i.Port
    OR (d.Port Is Null AND i.Port Is Null);

If you do not get the expected results with that query, confirm the "blank" Port values in both tables are really Null. If Port is a text field, it could contain empty strings, spaces, or non-printing characters ... any of those would be difficult to distinguish from Null by visual examination. You can run a simple query on each of the tables to check.

SELECT t.Port, IsNull(t.Port) AS port_is_null
FROM Imported AS t;

In results from that query, -1 represents True and 0 represents False.

Upvotes: 2

Related Questions