www.webdeveloper.com
Results 1 to 3 of 3

Thread: Problem with subquery

  1. #1
    Join Date
    Jul 2009
    Posts
    16

    Problem with subquery

    I'm having trouble figuring out what's wrong with my SQL query. If I execute the following, which returns a product id from one table that is also found in another products table, then it works properly.

    SELECT prod_id from products1 WHERE products1.prod_id IN (SELECT prod_id FROM products2)

    However what I really want to do is to return products that do not appear in the second table. I've tried the following but none of them work:

    SELECT prod_id from products1 WHERE products1.prod_id NOT IN (SELECT prod_id FROM products2)

    SELECT prod_id from products1 WHERE products1.prod_id <> ANY (SELECT prod_id FROM products2)

    SELECT prod_id from products1 WHERE products1.prod_id <> ALL (SELECT prod_id FROM products2)

    Can anyone help me figure what's wrong?

  2. #2
    Join Date
    Jan 2007
    Location
    Wisconsin
    Posts
    2,120
    Try ...
    Code:
    SELECT prod_id from products1 left join products2 on (products1.prod_id=products2.prod_id) WHERE products2.prod_id is null;

  3. #3
    Join Date
    Jun 2009
    Posts
    36
    Your NOT IN query should be correct. I just tried something similar and it worked. I don't know why it wouldn't be working for you.

    This is another way to do it using a join, but watch out for duplicates if the tables aren't one-to-one.
    SELECT products1.prod_id from products1 LEFT JOIN products2 ON (products1.prod_id=products2.prod_id) WHERE products2.prod_id IS NULL

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles