CODEWITHSUNDEEP
mysqlsql
Rana
Rana

Reputation: 178

mysql help to make a report

i have two table here is table_user and table_feedback like below 

table_user
| id | name |
|----|------|
| 1  | john |
| 2  | tony |
| 3  | mona |

table_feedback
| id | rate | user_id |   date   |
|----|------|---------|----------|
| 1  |   1  |   3     |2015-11-2 |
| 2  |   1  |   2     |2015-11-2 |
| 3  |   1  |   3     |2015-11-1 |

I wanted to show report by date from table_feedback including name and id from table_user and all user will be show if table_feedback didn't contain the user id then this will be return blank data. I have idea about inner join and here is my query. problem is that the query return 2 row only but i need 3 row including table_user id 1 with blank column rate. Here is my query below. 

SELECT 
 table_user.id,
 table_user.name,
 table_feedback.rate,
 table_feedback.date
FROM table_feedback
INNER JOIN table_user
 ON table_user.id = table_feedback.user_id
WHERE table_feedback = '2015-11-2'

expected_result_table
    | user_id | name | rate |   date   |
    |-------- |------|------|----------|
    | 1       |jony  |      |2015-11-2 |
    | 2       |tony  |   1  |2015-11-2 |
    | 3       |mona  |   1  |2015-11-2 |

Upvotes: 0

Views: 34

Answers (4)

slaakso
slaakso

Reputation: 9050

You need to do three modifications to your query:

  1. Use LEFT JOIN instead of INNER JOIN (to get all the users)
  2. Change the table order (first the table you want to get all rows from)
  3. As the user 1 (john) does not have any data in the second table, you cannot limit the rows in WHERE-clause. Do the limitation in JOIN instead, so it applies only to the rows that are matching the JOIN.

So:

SELECT 
  table_user.id,
  table_user.name,
  table_feedback.rate,
  table_feedback.date
FROM table_user
   LEFT JOIN table_feedback ON table_user.id = table_feedback.user_id and table_feedback.date = '2015-11-02'

Upvotes: 0

AdamMc331
AdamMc331

Reputation: 16691

The solution to this is an outer join. Anytime you find yourself thinking along the lines of "I need to see all rows from this table, regardless of a match in another table..." you should look to an outer join.

We can use an outer join to select all users, and link them to the feedbackTable in our JOIN clause. This will return null values for any columns in the table that don't match up. Try this:

SELECT u.id, u.name, t.rate, t.dateCol
FROM userTable u
LEFT JOIN feedbackTable t ON t.user_id = u.id AND t.dateCol = '2015-11-02';

Here is an SQL Fiddle example. As a side note, it is good practice not to name date columns date since that is a keyword in MySQL.

Edit based on your expected results:

To make sure the date column appears in each row, you can hardcode it into your select. If you choose to use a variable, you won't have to update the date twice each time, you can just update the declaration:

SET @reportDate = '2015-11-02';

SELECT u.id, u.name, t.rate, @reportDate
FROM userTable u
LEFT JOIN feedbackTable t ON t.user_id = u.id AND t.dateCol = @reportDate;

Here is an updated SQL Fiddle.

Upvotes: 2

Michal
Michal

Reputation: 2423

In order to include user without feedback you need to use LEFT OUTER JOIN

SELECT 
 table_user.id,
 table_user.name,
 table_feedback.rate,
 '2015-11-2'
FROM table_feedback
LEFT OUTER JOIN table_user
 ON table_user.id = table_feedback.user_id
WHERE table_feedback = '2015-11-2'

Upvotes: 0

Alex
Alex

Reputation: 17289

My guess is you need to use LEFT JOIN:

SELECT 
 table_user.id,
 table_user.name,
 table_feedback.rate,
 table_feedback.date
FROM table_user
LEFT JOIN table_feedback
ON table_user.id = table_feedback.user_id
  AND  table_feedback.date = '2015-11-2'

Upvotes: 1

Related Questions