0

I need to select returns result

SELECT wl.id FROM wishlist AS wl
LEFT JOIN wishlist_products AS wp ON wl.id = wp.wishlist_id

Only IF ALL of wp.product_id EXISTS in wishlist_products_bought.product_id

Thanks

  • 1
    Change LEFT JOIN to JOIN?? Or am I missing something in the question? – Rick James Feb 16 '17 at 1:04
  • Did any of the answers work for you? Could you try them? – joanolo Apr 2 '17 at 11:11
0

Try this:

SELECT wl.id 
FROM wishlist AS wl
LEFT JOIN wishlist_products AS wp ON wl.id = wp.wishlist_id
WHERE  wp.product_id in (select product_id from wishlist_products_bought)
  • The additional condition must be in the ON clause, because otherwice you will convert LEFT JOIN to an INNER JOIN. – Paul Spiegel Feb 14 '17 at 21:15
  • It does not satisfy condition Only IF ALL of wp.product_id EXISTS in wishlist_products_bought.product_id – andrei_sharov Feb 14 '17 at 22:27
0

Let's assume these are your table definitions (simplified to the minimum):

CREATE TABLE wishlist
(
    wishlist_id integer PRIMARY KEY
) ;

CREATE TABLE products
(
    product_id integer PRIMARY KEY
) ;

CREATE TABLE wishlist_products
(
    wishlist_id integer NOT NULL REFERENCES wishlist(wishlist_id),
    product_id integer NOT NULL  REFERENCES products(product_id),
    PRIMARY KEY (wishlist_id, product_id)
) ;

CREATE TABLE wishlist_products_bought
(
    wishlist_id integer NOT NULL REFERENCES wishlist(wishlist_id),
    product_id integer NOT NULL  REFERENCES products(product_id),
    PRIMARY KEY (wishlist_id, product_id)
) ;

And these are your data: 3 products, 2 wishlists. One of the wishlists (1) has all the products "bought", the other one doesn't (2).

INSERT INTO wishlist
VALUES (1), (2) ;

INSERT INTO products
VALUES (10), (20), (30) ;

INSERT INTO wishlist_products
VALUES (1, 10), (1, 20), (2, 10), (2, 20), (2, 30) ;

INSERT INTO wishlist_products_bought
VALUES (1, 10), (1, 20), (2, 20) ;

If I understand your objective: you're trying to identify the wishlist with all the products already "bought". That can be done with some set logic. Basically, you want to make sure that the set of all products from the wishlist is completely contained in the list of products bought (for the same wishlist). To check for full containment, you can do A - B = (empty set). This can be accomplished in SQL by using the EXCEPT. So, this query would do it:

SELECT
     wishlist_id
FROM
     wishlist w
WHERE
     NOT EXISTS
     ( SELECT product_id FROM wishlist_products wp        
        WHERE wp.wishlist_id = w.wishlist_id
       EXCEPT
       SELECT product_id FROM wishlist_products_bought wb 
        WHERE wb.wishlist_id = w.wishlist_id
     ) ;

You can check it at http://rextester.com/YOUE97199 (using PostgreSQL instead of MySQL).

There's one problem: this doesn't work with MySQL, because MySQL doesn't understand EXCEPT (nor any equivalent); and doesn't understand wishlist_id within the WHERE in the subquery. Which means an equivalent using NOT EXISTS cannot be used.

To overcome these limitations, we can use two functions GROUP_CONCAT and FIND_IN_SET, and use the following (more complicated that I like) query:

SELECT
    wishlist_id, product_id_list
FROM
(
    SELECT
        w.wishlist_id,
        (SELECT
             GROUP_CONCAT(wb.product_id SEPARATOR ',')
         FROM
             wishlist_products_bought wb
         WHERE
             wb.wishlist_id = w.wishlist_id
         ORDER BY
             product_id
         ) AS product_id_list
    FROM
        wishlist w
) AS q1 
WHERE
    NOT EXISTS
    (
        SELECT 
            product_id 
        FROM 
            wishlist_products wp
        WHERE 
            wp.wishlist_id = q1.wishlist_id
            AND FIND_IN_SET(product_id, product_id_list) = 0
    ) ;

You can also check it at http://rextester.com/OLKK85405

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.