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

    Join Date
    Mar 2013
    Posts
    10
    Rep Power
    0

    Problem writing Subquery


    I am trying to see if a product's model number or UPC code are listed inside a "do not show" table. I have written a query that looks to see if BOTH are found, but I need a query to looks for either one to be true.

    ModelNumber in Table A = ModelNumber in Table B
    or
    UPC in Table A = UPC in Table B

    Here is the query that isn't working. It looks for both to be true. How do I write this so that either are true.

    Select distinct A.ModelNumber, A.upc
    FROM A
    WHERE (A.ModelNumber, A.UPC) NOT IN (select B.UPC, B.ModelNumber from B)
    and A.ModelNumber= '#form.xyz#'

    Thank you in advance!

    Scott
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    10
    Rep Power
    0

    Follow up to above


    When a modelnumber is passed to this query I realize I could write this differently, but this query also needs to find ALL NON-Matching products when a modelnumber is not passed to this query.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Code:
    SELECT a.modelnumber
         , a.upc
      FROM a
    LEFT OUTER
      JOIN b
        ON b.modelnumber = a.modelnumber
        OR b.upc = a.upc
     WHERE a.modelnumber= '#form.xyz#'
       AND b.modelnumber IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    10
    Rep Power
    0

    Works with passed ModelNumber, but not without


    Thank you for posting this query. The query works great when the model number is passed (#form.xyz#), but it hangs when trying to find ALL products from TABLE A that are not in TABLE B using the same two fields (ModelNumber and UPC).

    For example, if I try to query all products from TABLE A that are not in TABLE B the query hangs. Even if I limit the query to the first 100 products (limit 0,100) it takes almost 5 seconds, making it unusable for a table with over 5,000 SKU's.

    Any suggestions on a query to use when trying to find more than just a few matches?

    Thanks again for your help.

    Scott
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    try this --
    Code:
    SELECT a.modelnumber
         , a.upc
      FROM a
    LEFT OUTER
      JOIN b
        ON b.modelnumber = a.modelnumber
     WHERE b.modelnumber IS NULL
    UNION
    SELECT a.modelnumber
         , a.upc
      FROM a
    LEFT OUTER
      JOIN b
        ON b.upc = a.upc
     WHERE b.upc IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo