#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    10
    Rep Power
    0

    Comparing two fields from one table with data in either field of other table


    I use Coldfusion along with MySQL and I am trying to check for a match between two fields in Table A with those same fields in Table B to see if either is a match. When I try to match both fields, the query hangs the server.

    Here's what I mean:

    Select ID, UPC
    From Table A
    where ID not in (select B.ID from Table B)
    and UPC not in (select B.UPC from Table B)

    In this example, Table "B" contains a list of ID's with UPC codes that I want to hide from the site. The query I am trying to write needs to compare these two fields from Table A against the two fields from Table B to make sure that NEITHER is a match.

    It's worth noting that table A has about 20,000 records and Table B about 10,000 records.


    ---
    I couldn't get the above to work correctly so I decided to do it in Two queries.

    1st Query loaded all 10,000 items from Table B into memory, and the second query does the comparison:

    1st) Select ID, UPC from Table B

    2nd) Select ID, UPC From Table A
    where ID not IN (#QuotedValuelist(Table B.ID)#)
    and
    Where UPC not IN (#QuotedValuelist(Table B.UPC)#)


    This query works if both ID and UPC have data, but if the UPC field is blank or Null then it won't work.

    Can anyone offer help on what I am doing wrong with either of these queries? I'd love to get the very first one working since I don't have to load all sku's into memory, but if I do need to do that then I can at least cache the query so it doesn't cause too many issues with memory.

    Thanks in advanced,

    Ron
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,124
    Rep Power
    4274
    Originally Posted by insight39
    I use Coldfusion ...
    me too, i love it

    here's a probably better performing "not exists" query for you --
    Code:
    SELECT a.id
         , a.upc
      FROM tablea AS a
    LEFT OUTER
      JOIN tableb AS b1
        ON b1.id = a.id
    LEFT OUTER
      JOIN tableb AS b2
        ON b2.upc = a.upc
     WHERE b1.id IS NULL
       AND b2.upc IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    10
    Rep Power
    0
    Wow, I thought this was going to do it, but for some reason the products from Table A that are IN table B are still showing up. (Regardless, thank you soooo much for the suggestion).

    Basically if a product "XYZ" and UPC of "123" (in Table A) is in Table B with either or both fields then I don't want the product to show up. With the above query they show up even if they ARE listed in Table B.

    Here's what I'm trying to accomplish. Hopefully this makes sense to someone other than me. :-)
    -----------------------------------------
    Table A Table B
    XYZ XYZ (ID)
    123 No Match (UPC)
    Since the XYZ was found in Table B - Don't show the item
    -----------------------------------------
    XYZ ----- (ID not found)
    123 123 (UPC Match found)
    Since the 123 was found in Table B - Don't show the item
    -----------------------------------------
    XYZ ----- (ID not found)
    NULL NULL (UPC Match found - but ignore since TableA.UPC field contains no data)
    Since the ID field found no match and the UPC field was blank or null - it's OKAY to show the item
    -----------------------------------------
    XYZ ----- (ID not found)
    123 ----- (UPC not found)
    Since the neither was found in Table B - OKAY to show the item
    --------------------------------------
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,124
    Rep Power
    4274
    Originally Posted by insight39
    With the above query they show up even if they ARE listed in Table B.
    i'd be willing to bet it's a data problem

    leading spaces, tab characters, missing dashes... whatever makes the UPCs not match even if they look the same on visual inspection
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    10
    Rep Power
    0
    Originally Posted by r937
    i'd be willing to bet it's a data problem

    leading spaces, tab characters, missing dashes... whatever makes the UPCs not match even if they look the same on visual inspection
    Hmmm. Both UPC fields are actually NULL values, so no spaces there. ID field is never blank since it is the Primary Key in both tables, which is why I want to match the UPC field as long as it's not NULL.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    10
    Rep Power
    0
    For some reason the query you shared is only showing items that are actually IN Table B, whereas I am trying to show items that are NOT in table B. I've tried "Right Join" as well, but no luck.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    10
    Rep Power
    0
    Originally Posted by insight39
    For some reason the query you shared is only showing items that are actually IN Table B, whereas I am trying to show items that are NOT in table B. I've tried "Right Join" as well, but no luck.
    Disregard everything I wrote after your post. I was doing something wrong since it seems to be working perfectly.

    Thank you, thank you!

    Ron
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    10
    Rep Power
    0
    Okay. When I try to run this query on the server - it just hangs. Runs slowly on my local server (which is typically a lot slower than the live server), but I can't get it to complete on the live server. Back to square one.
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,124
    Rep Power
    4274
    Originally Posted by insight39
    Back to square one.
    can you do a SHOW CREATE TABLE on the live server so we can see which indexes have been defined
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo