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
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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)
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.
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