5

MySQL select 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. A shop can have many products. A product can be in many shops.

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

I am part way there with 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)

That gets all the products that have a category id of 2 and also products with a category id of 8, 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 make it 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)

Which is now distinct but does not show enough information about the product or category. I want to show as much information as possible in each row.

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

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

BeniaminoBaggins
  • 11,202
  • 41
  • 152
  • 287

1 Answers1

2

Let's say that you want to list all product information. If you dont want the products to repeat, you may use 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 product data and list which categories and shops the product is related to, then you may use join and some very handy 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
Walter_Ritzel
  • 1,387
  • 1
  • 12
  • 16
  • Thanks! For some reason it displays this in the results row of one product, for categories and shops: 2, 8, 8, 2 1, 2, 1, 2` eg it has duplicate shops and categories for one product. I have posted a photo of the entries in my product_category table...Am I doing something wrong there, that is making it duplicate a single products' categories and shops? – BeniaminoBaggins Apr 30 '16 at 01:20
  • This happens because the product is present in 2 shops and is related to 2 categories: 2 x 2 = 4 occurrences of the product. The solution is to add distinct on group_concat. See my edit on the answer. – Walter_Ritzel Apr 30 '16 at 01:31