Mysql - select from multiple tables without producing duplicate data

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!

possible duplicate of [Selecting across multiple tables with UNION](http://stackoverflow.com/questions/6066197/selecting-across-multiple-tables-with-union) — worldofjr, Dec 07 '14 at 08:03
@worldofjr Union queries need to have the same number of columns, so that wouldn't work in my case. — hmzfier, Dec 07 '14 at 08:58

score 1 · Accepted Answer · edited May 23 '17 at 10:25

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

edited May 23 '17 at 10:25

Community

1
1

answered Dec 07 '14 at 07:17

Vinay

61
6

@MisterDood i agree, will try to correct it – Vinay Dec 07 '14 at 07:44

Konstantin · Answer 2 · 2014-12-07T08:25:10.460

There are ways to discard duplicates (SELECT DISTINCT, UNION, GROUP BY) but it is not clear whether users update existing rating or create new ones. And what you want to see: the last rating or the average one

On the other note - i would change your entire setup:

order table would contain order_id, customer_idand other order related stuff like order_date
products table that would describe each of your dishes and their info like price, description etc
order_products table with fields order_id and prduct_id
if users rate products then your rating table would need at least product_id, customer_id, rate_value. I'd also add ratingDate That way you can get averages or select the last one by Max(ratingDate)

score 0 · Answer 3 · answered Dec 07 '14 at 08:01

0

I think you need to add an orderID field to the CustomerRating table else there is no way to relate an item to its rating.

answered Dec 07 '14 at 08:01

brobas

596
4
5

Thanks, but I'm not looking to relate an item to its rating. – hmzfier Dec 07 '14 at 09:40

Mysql - select from multiple tables without producing duplicate data

3 Answers3

Linked