UNION MySQL, Check if its bold

Question

Hello guys i have that SQL:

SELECT p.* FROM products p WHERE required_product_id IS NULL 
UNION ALL
SELECT  p.* FROM products p, orders o WHERE p.required_product_id = o.product_id 
AND o.user_id = 1
UNION DISTINCT
SELECT p.`*` FROM products p, orders o WHERE p.id NOT IN (SELECT product_id FROM orders WHERE product_id = p.id AND o.user_id = 1)
AND p.max_buys = 1;

This query first checking if item is purchased and show next item! i want to check if user is purchased that product to return only that product that user is not bought it

table structure = Products: http://prntscr.com/k6ogp4 ,Orders: http://prntscr.com/k6ogrz

max_buys colum on products (if it 1 it can buy it once , if its 0 it can be buyed many times)

So here is data in orders http://prntscr.com/k6oioz Product data: http://prntscr.com/k6oiri — Lubomir Stankov, Jul 15 '18 at 13:47
I want if product.id exist in orders to not show it and show only that product that is not purchased! (required_product_id table in product is id of first product that can be purchased first to unlock product with id 2) — Lubomir Stankov, Jul 15 '18 at 13:48
Result must be if user is purchased first rank to hide it from list and show nextone that must buy — Lubomir Stankov, Jul 15 '18 at 13:49
No pictures, thanks. See [Why should I provide an MCVE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Jul 15 '18 at 16:35

mehran monster · Accepted Answer · 2018-07-15T17:17:53.590

1

In your case I prefer to have a flexible query to manage requirements and also I think that UNION is not required in this case [if your description is complete].

SELECT w.* from (
  SELECT 
    (SELECT count(o.product_id) FROM orders o WHERE o.product_id = p.id AND o.user_id = 1) bought_count,
    (SELECT count(q.product_id) FROM orders q WHERE q.product_id = p.required_product_id AND q.user_id = 1)  order_depend,
    p.* 
  FROM products p  ) w
where 
  (order_depend>0 or required_product_id is null) and -- unlock order depended products
  (max_buys=0 or   -- can buy more than once
  bought_count=0)  -- or not bought yet
order by 
    order_depend desc, -- dependent products to ordered products in first level
    bought_count asc,  -- not bought products in second level
    recommended desc   -- recommended products in third level

You can also manage any other order according to your requirement.

edited Jul 15 '18 at 17:17

answered Jul 15 '18 at 17:06

mehran monster

82
6

Where come bought_count table? – Lubomir Stankov Jul 15 '18 at 17:12
Yeah it work very well , one thing where come this tables http://prntscr.com/k6q6st for what they needed? I mean bought_count order_depend – Lubomir Stankov Jul 15 '18 at 17:19
pls wait some minutes. I am making this query in my mind only and cant check errors systematic I want to check syntax! – mehran monster Jul 15 '18 at 17:20
ok i found another error, it give me all products when doens't have anything in orders, i want to be upgradable like first product show in list only and when u buy it it will show second product – Lubomir Stankov Jul 15 '18 at 17:21
pls attention to my last edit in the condition specially paranthesis. – mehran monster Jul 15 '18 at 17:22
If there is no any order it must show required_product_id is null – mehran monster Jul 15 '18 at 17:24
yeah it work now but, it show again first product when second is unlocked http://prntscr.com/k6q90p – Lubomir Stankov Jul 15 '18 at 17:25
nvm my mistake! Thank you man your awesome all work like charm – Lubomir Stankov Jul 15 '18 at 17:27

UNION MySQL, Check if its bold

1 Answers1