How to query from 3 tables like this

Question

Suppose I have three table project, and I want to make a html with the correct values from database with php.

I have tried like this:

select (SELECT group_concat(DISTINCT wine_name) FROM wine) as wine_name UNION SELECT username FROM user;

Evaluate table

-----------------------------------------------------------------------------
|     ev_id     |     user_id       |     wine_id       |        point
-----------------------------------------------------------------------------
|       1       |         1         |       1           |         80
|       2       |         2         |       1           |         67
|       3       |         1         |       2           |         87
|       5       |         2         |       2           |         97
|       6       |         2         |       3           |         81
|       7       |         3         |       2           |         99
|       8       |         3         |       3           |         66
|       9       |         3         |       1           |         79
-----------------------------------------------------------------------------

Wine Table

-----------------------------------------------------------------------------
|     wine_id   |     wine_name     |     wine_type     |     wine_color
-----------------------------------------------------------------------------
|       1       |   Dominio Campo   |        sweet      |         red
|       2       |   Guitian Godello |        dry        |         white
|       3       |   Cosme Palacio   |        dry        |         red
|       4       |   Azpilicueta     |        sweet      |         red
|       5       |   Parotet Vermell |        sweet      |         white
-----------------------------------------------------------------------------

User Table
-----------------------------------------------------------------------------
|     user_id   |     user name     |        name       |       email
-----------------------------------------------------------------------------
|       1       |     user1         |        dsa        |     asd@asd.com
|       2       |     user2         |        dsd        |     abd@asd.com 
|       3       |     user3         |        dss        |     acd@asd.com
|       4       |     user4         |        sdd        |     add@asd.com
|       5       |     user5         |        ssd        |     aed@asd.com 
-----------------------------------------------------------------------------

Here I want to query from three table like this and display the result.

-----------------------------------------------------------------------------
|                |   Dominio Campo   |   Guitian Godello |   Cosme Palacio   |
-----------------------------------------------------------------------------
|    user1       |       80          |       87          |       77          |
|    user2       |       67          |       97          |       81          |
|    user3       |       79          |       99          |       66          |
-----------------------------------------------------------------------------

Also you might want to consider to do the displaying in the application layer. — Raymond Nijland, Mar 25 '19 at 16:29
Yeah, just do the presentation stuff in your application code. — Strawberry, Mar 25 '19 at 16:44
This question has been closed as a duplicate, but I really wouldn't use the methods described at the linked answer. Presentation is better handled in the presentation layer. That's what it's for. — Strawberry, Mar 25 '19 at 23:58

score 1 · Accepted Answer · answered Mar 25 '19 at 16:52

1

This is all the query you need:

SELECT w.*
     , e.user_id
     , e.point 
  FROM wine w 
  JOIN evaluate e 
    ON e.wine_id = w.wine_id 
 ORDER 
    BY e.wine_id
     , user_id;

Everything else can and should be done in your application code.

answered Mar 25 '19 at 16:52

Strawberry

33,750
13
40
57

I need the username too. – szaller Mar 25 '19 at 18:01
In point of fact, that wasn't included in your result, but either way, now you know what to do – Strawberry Mar 25 '19 at 23:12

How to query from 3 tables like this

1 Answers1