#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    28
    Rep Power
    0

    Smile Selecting only values from one table that do NOT exist in another?


    Selecting only values from one table that do NOT exist in another?

    Which means, I have two tables, one of the tables already contains part of the 2nd tables data, and I now want a sql-query (for access) that will return the rows that do NOT exist in the first table (from the 2nd table).

    Is that easy? It sounds easy, but I just can't figure it out
  2. #2
  3. Wiking
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Sep 2000
    Location
    Sweden
    Posts
    3,608
    Rep Power
    26
    Something like
    Code:
    SELECT 
      t2.*
    FROM
        table1
      LEFT JOIN
        table2
      ON 
        table1.id = table2.id
    WHERE 
      table1.id IS NULL
    //NoXcuz
    UN*X is sexy!
    who | grep -i blonde | date; cd ~; unzip; touch; strip; finger; mount; gasp; yes; uptime; umount; sleep
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    28
    Rep Power
    0
    This is what it would look like if I understood it correctly, however, it returns nothing.

    Probably because I have no null's in my table..
    I need the rows that do not exist in the first table, but these rows do not contain nulls or any other character to check by.

    SELECT
    EXCEL_TEMP_PUR.*
    FROM
    EXCEL_118
    LEFT JOIN
    EXCEL_TEMP_PUR
    ON
    ((EXCEL_118.PROJID = EXCEL_TEMP_PUR.PROJID) AND (EXCEL_118.WORK_NUMBER = EXCEL_TEMP_PUR.WORK_NUMBER)
    AND (EXCEL_118.LAJI = EXCEL_TEMP_PUR.LAJI) AND (EXCEL_118.ORDERID = EXCEL_TEMP_PUR.ORDERID)
    AND (EXCEL_118.ORDER_ROWNUM = EXCEL_TEMP_PUR.ORDER_ROWNUM) AND (EXCEL_118.ITEMID = EXCEL_TEMP_PUR.ITEMID)
    AND (EXCEL_118.ITEMNAME = EXCEL_TEMP_PUR.ITEMNAME) AND (EXCEL_118.COMPANYNAME = EXCEL_TEMP_PUR.COMPANYNAME)
    AND (EXCEL_118.SIDOTUT = EXCEL_TEMP_PUR.SIDOTUT))
    WHERE
    EXCEL_118.PROJID IS NULL
    Last edited by Hena; July 7th, 2003 at 03:10 AM.
  6. #4
  7. Wiking
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Sep 2000
    Location
    Sweden
    Posts
    3,608
    Rep Power
    26
    Sorry, I mixed things up a bit...
    It should be something like this instead (swap table1 and table2 in the FROM part)
    Code:
    SELECT 
      t2.*
    FROM
        table2
      LEFT JOIN
        table1
      ON 
        table1.id = table2.id
    WHERE 
      table1.id IS NULL
    Also, the NULLs doesn't exist in your tables, but in your result set when you do a left join. This left join means that 'give me everything from the left table joined with the right table regardless if there's a match or not'. And when you add the IS NULL part, you filter it out so that you see only those entries in the left table that doesn't have a corresponding entry in the right table.

    And you shouldn't use all those fields to join the tables, but stick to the primary key that determines the uniqueness of the row (probably PROJID in your case).

    //NoXcuz
    UN*X is sexy!
    who | grep -i blonde | date; cd ~; unzip; touch; strip; finger; mount; gasp; yes; uptime; umount; sleep
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    28
    Rep Power
    0
    Good grief, it was that simple then?

    As in, it works, once I switched the tables as you told me to, and now it returns the rows needed...

    Thank you NoXcuz
    (tack, du är från sverige såg jag just )
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    28
    Rep Power
    0
    Hey again, hope you are ready to help again, this time it's a bit trickier..

    The same query does the job, however it returns 93 rows too much, which I can't explain in any way...

    So, here we go:
    SELECT EXCEL_TEMP_PUR.*
    FROM EXCEL_TEMP_PUR LEFT JOIN EXCEL_118 ON (EXCEL_118.PROJID = EXCEL_TEMP_PUR.PROJID) AND (EXCEL_118.WORK_NUMBER = EXCEL_TEMP_PUR.WORK_NUMBER) AND (EXCEL_118.LAJI = EXCEL_TEMP_PUR.LAJI) AND (EXCEL_118.ORDERID = EXCEL_TEMP_PUR.ORDERID) AND (EXCEL_118.ORDER_ROWNUM = EXCEL_TEMP_PUR.ORDER_ROWNUM)
    WHERE (EXCEL_118.PROJID IS NULL) AND (EXCEL_118.WORK_NUMBER IS NULL) AND (EXCEL_118.LAJI IS NULL) AND (EXCEL_118.ORDERID IS NULL) AND (EXCEL_118.ORDER_ROWNUM IS NULL);
    That is the query I am using right now, EXCEL_118 table contains the matched values that I want to exclude in this query, exclude from EXCEL_TEMP_PUR table.

    EXCEL_118 contains 5919 rows while
    EXCEL_TEMP_PUR has 61937 rows
    which gives a total of 56018 unmatched rows (rows that were not included in EXCEL_118 table).

    Thus, the query above, _should_ return 56018 rows, however it does not, it returns too many?!, 56111 rows.
    That is 93 rows too many.

    I have tried doing the query without checking for null's in some of those fields, for example only checking PROJID field for null's, it doesn't matter, the result will still be 56111 rows.


    Further information about EXCEL_TEMP_PUR table (the source table for all the data important to this query).
    It contains null values in WORK_NUMBER fields, 15711 rows
    and null values in LAJI field, 205 rows
    and null values in ORDERID field, 42 rows.

    Can any of those nulls in the source table affect the query, or where might the problem be?

    Hena
  12. #7
  13. Wiking
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Sep 2000
    Location
    Sweden
    Posts
    3,608
    Rep Power
    26
    I think the query does it's job, so the problems must be somewhere in your data. You have something in your tables that you're not expecting. Also, I'm not fully aware of your table layout, which adds to the 'trickieness'...
    Simply put: You need to find out which those 93 rows are, so you can determine what to do with them or how to change the query.
    Try something like this and see if you get any results:
    Code:
    SELECT 
      EXCEL_TEMP_PUR.PROJID, 
      COUNT(EXCEL_TEMP_PUR.PROJID) AS number 
    FROM 
        EXCEL_TEMP_PUR 
      LEFT JOIN 
        EXCEL_118 
      USING PROJID 
    WHERE 
      EXCEL_118.PROJID IS NULL 
    GROUP BY
      EXCEL_TEMP_PUR.PROJID
    HAVING 
      number >= 2
    Also, what happens if you remove the fields that can be NULL from the join part, and only use PROJID to join them. Still the same amount of rows in the result set?
    Or if you use a single field to join them by, run the query once for each field and change that field in the query to see if the queries return the same amount of rows.

    //NoXcuz
    UN*X is sexy!
    who | grep -i blonde | date; cd ~; unzip; touch; strip; finger; mount; gasp; yes; uptime; umount; sleep
  14. #8
  15. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    28
    Rep Power
    0
    So true, the query works..
    The underlying data was the problem, in fact, there is a group by problem that I have to solve now instead...
    The group by in this query only uses projid to order_rownum to group, while the one that made the TEMP_PUR table used 3 more fields (because sql (or access) requiers them to be in).

    I'll have to solve this in someway...

    You wouldn't happen to know how to easily avoid the problem I have in this query do you?

    INSERT INTO EXCEL_TEMP_PUR ( PROJID, WORK_NUMBER, LAJI, ORDERID, ORDER_ROWNUM, ITEMID, ITEMNAME, COMPANYNAME, SIDOTUT )
    SELECT PUR_ORDERBATCH.PROJID, PUR_ORDERBATCH.WORK_NUMBER, Left(DLG_ITEM.ITEM_TYPE_NAME,2) AS LAJI, PUR_ORDERBATCH.ORDERID, PUR_ORDERBATCH.ORDER_ROWNUM, PUR_ORDERBATCH.ITEMID, PUR_ORDERBATCH.ITEMNAME, PUR_ORDERBATCH.COMPNAME, Sum(PUR_ORDERBATCH.AMOUNT_BATCH) AS SumOfAMOUNT_BATCH
    FROM PUR_ORDERBATCH, DLG_ITEM
    WHERE (((PUR_ORDERBATCH.PROJID) Is Not Null) AND ((DLG_ITEM.ITEMID)=[PUR_ORDERBATCH].[ITEMID]))
    GROUP BY PUR_ORDERBATCH.PROJID, PUR_ORDERBATCH.WORK_NUMBER, Left(DLG_ITEM.ITEM_TYPE_NAME,2), PUR_ORDERBATCH.ORDERID, PUR_ORDERBATCH.ORDER_ROWNUM, PUR_ORDERBATCH.ITEMID, PUR_ORDERBATCH.ITEMNAME, PUR_ORDERBATCH.COMPNAME;
    I want to NOT group with ITEMID, ITEMNAME, COMPNAME, however, if I try to just remove those fields, access will say: "You tried to execute a query that doesn't include the specified expresssion 'ITEMID' as part of an aggregate function."

    Any idea?

    Hena
  16. #9
  17. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    28
    Rep Power
    0
    I'm sure someone has had the same trouble that I am.. come on, don't be shy, just tell me the solution to this
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2001
    Location
    High above the mucky-muck (Columbus, OH)
    Posts
    266
    Rep Power
    14
    re: the original query -- use NOT EXISTS instead (if Access supports it, I think they do). The LEFT OUTER JOIN .. NULL thing is a MySQL workaround for not having subqueries.

    Code:
    SELECT *
      FROM excel_temp_pur a
     WHERE NOT EXISTS( SELECT *
                         FROM excel_118 b
                        WHERE a.projid = b.projid )
    Can you enclose your query in the 'code' tags instead of quote so it can be easily read? Also re-phrase your question -- I don't know what you want!
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Location
    sri lanka
    Posts
    1
    Rep Power
    0
    Originally Posted by NoXcuz
    Sorry, I mixed things up a bit...
    It should be something like this instead (swap table1 and table2 in the FROM part)
    Code:
    SELECT 
      t2.*
    FROM
        table2
      LEFT JOIN
        table1
      ON 
        table1.id = table2.id
    WHERE 
      table1.id IS NULL
    Also, the NULLs doesn't exist in your tables, but in your result set when you do a left join. This left join means that 'give me everything from the left table joined with the right table regardless if there's a match or not'. And when you add the IS NULL part, you filter it out so that you see only those entries in the left table that doesn't have a corresponding entry in the right table.

    And you shouldn't use all those fields to join the tables, but stick to the primary key that determines the uniqueness of the row (probably PROJID in your case).

    //NoXcuz
    you helped me BIG TIME!! tanku sooo much!!

IMN logo majestic logo threadwatch logo seochat tools logo