MySQL从3个表中选择不同的产品 [英] MySQL select distinct products from 3 tables

问题描述

MySQL从3个表中选择.

MySQL select from 3 tables.

我有以下5张桌子:

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.

我想选择category.c_id = 2或category.c_id = 8且shop.s_id = 1或shop.s_id = 2的所有产品.

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.

我与此相伴:

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)

这将获得所有类别ID为2的产品以及类别ID为8的产品，但是如果同时具有category.c_id = 8和category.c_id = 2，它将获得相同的产品两次.

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.

下一步是仅获取shop.s_id = 1或shop.s_id = 2的商店.

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!

推荐答案

假设您要列出所有产品信息.如果您不希望产品重复，则可以使用IN子句.

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))

现在，如果要获取所有产品数据并列出与产品相关的类别和商店，则可以使用join和一些非常方便的功能.

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

这篇关于MySQL从3个表中选择不同的产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！