显示多对多查询的多个值

I have three table:

我有三张桌子:

user
products
user selected products

用户选择的产品

A user can select multiple products.

用户可以选择多个产品。

I want to write an SQL query to find all users, and also list all of the products they have selected.

我想编写一个SQL查询来查找所有用户,并列出他们选择的所有产品。

This is my query so far:

这是我目前的查询:

 SELECT
    u.user_id,CONCAT(u.firstName,' ',u.lastName)as name, u.email,
    (SELECT
        p.product_id
    FROM  
        user_selected_products p
    WHERE
        p.user_id= u.user_id
    )as productsSelected

FROM 
users u;

And here are my tables:

这是我的表格:

CREATE TABLE users(
   user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, 
   firstName VARCHAR(30) NOT NULL,
   lastName VARCHAR(40) NOT NULL,
   email VARCHAR (80) NOT NULL   
);       

CREATE TABLE products (
   product_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, 
   product_name VARCHAR(150) NULL,
   PRIMARY KEY (product_id),
   INDEX productName( product_name )
); 

CREATE TABLE user_selected_products (
   user_id MEDIUMINT UNSIGNED NOT NULL , 
   product_id MEDIUMINT UNSIGNED NOT NULL , 
   INDEX selectedProductName(user_id,product_id )
);

I am getting the following error:

我收到以下错误:

: Subquery returns more than 1 row

:子查询返回超过1行

the subquery is not working because I am trying to return multiple values. What is the proper way to write this query?

子查询无法正常工作,因为我正在尝试返回多个值。编写此查询的正确方法是什么?

UPDATE

I want to see all the products that a user has selected

我想查看用户选择的所有产品

the table user_selected_products will have multiple results in it. i.e a user might select multiple products. The query therefore needs to return all the results for the multiple products that a user might select.

表user_selected_products将包含多个结果。即用户可能选择多个产品。因此,查询需要返回用户可能选择的多个产品的所有结果。

2 个解决方案

#1

First, the problem; The reason you are getting this error is pretty self explanatory, you have a subquery that returns multiple values, but are trying to put it as one cell of your result table, which is not allowed. Cells cannot have more than one value.

一,问题;你得到这个错误的原因是非常自我解释的,你有一个子查询返回多个值,但是试图将它作为结果表的一个单元格,这是不允许的。单元格不能有多个值。

The solution does not require a subquery. Your user_selected_products table has everything you need. If you want to see user and product information along side of that, you can just use joins, like this:

该解决方案不需要子查询。您的user_selected_products表包含您需要的所有内容。如果您想查看用户和产品信息,可以使用联接,如下所示:

SELECT u.user_id, CONCAT(u.firstName, ' ', u.lastName) AS name, u.email, p.product_id
FROM users u
JOIN user_selected_products p
ON p.user_id = u.user_id

This will return multiple rows for each user/product combination. If you would like to see all product_ids in the same cell, you can use the GROUP_CONCAT() function which will separate them by commas. You just need to them group by user_id, like this:

这将为每个用户/产品组合返回多行。如果您希望在同一单元格中看到所有product_ids,可以使用GROUP_CONCAT()函数,它将用逗号分隔它们。你只需要按user_id进行分组,如下所示:

SELECT u.user_id, CONCAT(u.firstName, ' ', u.lastName) AS name, u.email,
   GROUP_CONCAT(p.product_id) AS selectedProducts
FROM users u
JOIN user_selected_products p ON p.user_id = u.user_id
GROUP BY u.user_id;

EDIT

Here is a shortened SQL Fiddle example that demonstrates how GROUP_CONCAT works. If this is not how you want the data displayed, there are ways to change the delimiter for concatenated items and you can design it how you want.

这是一个缩短的SQL Fiddle示例,演示了GROUP_CONCAT的工作原理。如果这不是您希望显示数据的方式,则有多种方法可以更改连接项的分隔符,您可以根据需要进行设计。

Also, if you want to see a row for each user, regardless of whether or not they have any selected products, you can do an OUTER JOIN to the users table.

此外,如果要查看每个用户的行,无论他们是否有任何选定的产品,您都可以对用户表执行OUTER JOIN。

#2

You don't need subquery, you can use left join as below

您不需要子查询,可以使用左连接,如下所示

SELECT
  u.user_id,CONCAT(u.firstName,' ',u.lastName)as name, u.email,
  p.product_id as productsSelected                        
FROM users u
LEFT JOIN user_selected_products p on p.user_id= u.user_id;

if you want to see all praodutcs in one row try to use group_concat

如果你想看到一行中的所有praodutcs尝试使用group_concat

SELECT
u.user_id,CONCAT(u.firstName,' ',u.lastName)as name, u.email,
group_concat(p.product_id) as productsSelected                        
FROM users u
LEFT JOIN user_selected_products p on p.user_id= u.user_id
GROUP BY u.user_id;

#1