Mysql - 从多个表中选择而不产生重复数据[关闭]

Question

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!

Answer 1

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