CODEWITHSUNDEEP
mysql
Blaskovic
Blaskovic

Reputation: 356

Specific MySQL issue with JOIN

I have a product table:

product_id
shop_id -> id from shop table
product_pair = there is product_id, if it is paired

Then I have a shop table:

shop_id

And finally a shipping table:

shop_id -> id from shop table
country_id -> id of country

And I want to find the products which can be shipped to country_id 60

It's no problem, if it's not paired.. Like:

SELECT p.*, c.*, p.product_name AS score
FROM (`rcp_products` p)
JOIN `rcp_shipping` s ON `s`.`shop_id` = `p`.`shop_id` AND s.country_id = 60
JOIN `rcp_category` c ON `c`.`cat_id` = `p`.`cat_id`
WHERE `p`.`cat_id` =  '7'
AND `p`.`product_price_eur` > 0
AND `p`.`product_mark_delete` =  0
ORDER BY `score` asc
LIMIT 10

(There are some additional WHERE's and another columns, which I think haven't got influence)

Now, I have paired products. So, in a table with products is something like this:

product_id | product_name | product_pair | shop_id
1          | Abc          | 0            | 0
2          | Def          | 1            | 3
3          | Ghi          | 1            | 2

So, products 2 and 3 are paired to product 1. Now, I have no idea how to get country_id for product_id = 1 in that SQL that I posted above.

Maybe my database structure is not the best :) But how can I do it better?

Thank you.

Upvotes: 0

Views: 122

Answers (1)

Aurimas
Aurimas

Reputation: 2493

Overall, the idea that you need to use here is self-join - that's how you can find the pairs of products. After that it's just simple WHERE conditions.

The core query (the one that just finds the pairs from a specific shop) would look like this:

SELECT DISTINCT A.product_id as P1, B.product_id as P2, A.shop_id as S1, B.shop_id as S2
FROM products A, products B
WHERE (A.product_pair = B.product_id OR A.product_pair = 0) //find pair and non-paired
      AND (A.product_id > B.product_id) //ensure no duplicates (e.g. A&B and B&A)
      AND (A.shop_id = B.shop_id) //ensure that both can be found in the same shop
      AND A.shop_id = YOUR_SHOP_ID //filter to specific shop

This should satisfy the conditions when products are sold in more than 1 shop, otherwise the query could probably become a bit shorter / easier.

Upvotes: 1

Related Questions