MySQL selects individual products from 3 tables

Question

MySQL selects from 3 tables.

I have these 5 tables:

CREATE TABLE `category` ( `c_id` int(6) NOT NULL AUTO_INCREMENT, `name` varchar(40) NOT NULL, PRIMARY KEY (c_id) ); CREATE TABLE `product` ( `p_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(40) NOT NULL, `brand` varchar(30) NOT NULL, `image_path` varchar(100) DEFAULT NULL, PRIMARY KEY (p_id) ); CREATE TABLE `shop` ( `s_id` int(6) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `country` varchar(30) NOT NULL, `province` varchar(30) NOT NULL, `city` varchar(30) NOT NULL, `suburb` varchar(30) NOT NULL, `street` varchar(40) DEFAULT NULL, `streetNumber` varchar(40) DEFAULT NULL, `postalCode` int(4) DEFAULT NULL, PRIMARY KEY (s_id) ) ; CREATE TABLE product_category ( p_id INT NOT NULL, c_id INT NOT NULL, PRIMARY KEY (p_id, c_id), FOREIGN KEY (p_id) REFERENCES Product(p_id) ON UPDATE CASCADE, FOREIGN KEY (c_id) REFERENCES Category(c_id) ON UPDATE CASCADE ); CREATE TABLE product_shop ( p_id INT NOT NULL, s_id INT NOT NULL, PRIMARY KEY (p_id, s_id), FOREIGN KEY (p_id) REFERENCES product(p_id) ON UPDATE CASCADE, FOREIGN KEY (s_id) REFERENCES shop(s_id) ON UPDATE CASCADE ); 

Basically, a product can have many categories. A category can be assigned to many products. There may be many products in a store. The product may be in many stores.

I would like to select all products where category.c_id = 2 or category.c_id = 8 and shop.s_id = 1 or shop.s_id = 2.

I partially participate in this:

 select * from product inner join product_category on product_category.p_id=product.p_id where (product_category.c_id=2) or (product_category.c_id=8) 

This gets all products that have category 2 identifier, as well as products with category 8 identifier, but it gets the same product twice if it has both category.c_id = 8 and category.c_id = 2.

Then I tried this to get unique products:

 select DISTINCT(product.p_id) as product from product inner join product_category on product_category.p_id=product.p_id where (product_category.c_id=2) or (product_category.c_id=8) 

What is now different, but does not contain enough information about the product or category. I want to show as much information as possible on each line.

And the next step is to get only those where shop.s_id = 1 or shop.s_id = 2.

Can someone help me get there or get closer? Thanks!

Answer 1

Say you want to list all the product information. If you do not want the products to be repeated, you can use the IN clause.

 select p.* from product p where p.p_id in (select c.p_id from product_category c where c.c_id in (2,8)) and p.p_id in (select s.p_id from product_shop s where s.s_id in (1,2)) 

Now, if you want all the product data and a list of categories and stores to which the product belongs, you can use the combination and some very convenient functions.

 select p.p_id, p.`name`, p.brand, GROUP_CONCAT(DISTINCT c.c_id SEPARATOR ', ') as categories, GROUP_CONCAT(DISTINCT s.s_id SEPARATOR ', ') as shops from product p inner join product_category c on p.p_id = c.p_id inner join product_shop s on p.p_id = s.p_id where c.c_id in (2,8) and s.s_id in (1,2) group by p.p_id, p.`name`, p.brand