mysql 选择不重复_MySQL-从多个表中选择而不产生重复数据

I have three tables and I would like to select from the tables without producing duplicates.

The table are as follows:

Customers

id | name | lastName

---------------------------------------

1 | john | doe

2 | helen | keller

Orders

The userID column is a foreign key that references John Doe, so John orders 3 items.

id | userID | order

---------------------------------------

1 | 1 | pizza

2 | 1 | pasta

3 | 1 | lasagna

CustomerRating

The userID column is a foreign key that references John Doe, so John leaves 5 reviews.

id | userID | rating | comment

-------------------------------------------------

1 | 1 | 5/5 | was good

2 | 1 | 5/5 | excellent

3 | 1 | 4/5 | great

4 | 1 | 4/5 | great

5 | 1 | 4/5 | great

How would I select from the 3 tables where I can get a return results that look like this?

id | name | lastName | order | rating

-----------------------------------------------------------------

1 | john | doe | pasta | 5/5

| | | pizza | 5/5

| | | lasagna | 4/5

| | | | 4/5

| | | | 4/5

I've tried joining these tables, but since John has left 5 reviews and only ordered 3 times, the id, name,lastName, and order columns gets filled with duplicate data.

Thanks!

解决方案

I don't have any experience in MySQL but I assume that it works similar to MSSQL.

So the format in which you are expecting the output is not possible. You can rather get the order and rating column values as comma separated

Here is a similar kind of question that might help you

including example based on link

try something like this

SELECT Customers.id, Customers.name, Customers.lastName,

GROUP_CONCAT(Orders.order) OrderedDishes,

GROUP_CONCAT(CustomerRating.rating) RatingsGiven

FROM

..... rest of your query .....