CODEWITHSUNDEEP
sqljoin
Mihai Matei
Mihai Matei

Reputation: 24276

Unexpected results from an sql query which uses RIGHT JOIN

I have an sql query which seems to return an unexpected blank row. This query has an right join to another table but when return the rows it returns me a null value..

  SELECT e.error_id, e.user_id, e.error_title, e.error_content, e.error_answers, 
         e.error_votes, e.error_views, e.error_added_date, u.user_name
  FROM errors e
  JOIN users u
    ON u.user_id=e.user_id
  RIGHT JOIN answers a
    ON a.error_id=e.error_id AND a.answer_accepted='1'
  GROUP BY e.error_id
  ORDER BY e.error_added_date DESC

This query should return me one single row but It returns me the expected row and one row with blank values.. Why is that?

Entries

+----------------------------------------------------------------------------------------------------+
answer_id | error_id | user_id | answer_content | answer_accepted | answer_votes | answer_added_date |
1         | 3        | 1       | text           | 0               | 0            | 2013-01-31 12:49:12
2         | 3        | 1       | text           | 1               | 1            | 2013-01-31 12:52:29
3         | 3        | 1       | text           | 0               |-1            | 2013-01-31 12:53:45
4         | 2        | 1       | text           | 0               |-1            | 2013-01-31 12:53:45
+----------------------------------------------------------------------------------------------------+

Results:

+-------------------------------------------------------------------------------+
|   1 | 1    | text |  3   | 0    |  2   |  2013-01-29 16:56:20  |  Mihai Matei |
|NULL | NULL | NULL | NULL | NULL | NULL |       NULL            |  NULL        |
+-------------------------------------------------------------------------------+

Upvotes: 0

Views: 73

Answers (1)

Jeff S
Jeff S

Reputation: 7484

This is happening because you are doing a Right Join. I think you want a Left Join or an Inner Join.

A right join means return all the table rows on the right and the matching rows from the left. Because your result set doesn't include any columns from the answers table (the right table), you can get a set of all nulls. In other words, there is a row in the answers table with no corresponding rows in the error and users table.

Given the additional criteria in the comment on this answer, here's the query I would try:

  SELECT e.error_id, e.user_id, e.error_title, e.error_content, e.error_answers, 
         e.error_votes, e.error_views, e.error_added_date, u.user_name
  FROM errors e
  JOIN users u
    ON u.user_id=e.user_id
  LEFT JOIN answers a
    ON a.error_id = e.error_id and a.answer_accepted = '1'
  WHERE a.answer_id is null
  GROUP BY e.error_id
  ORDER BY e.error_added_date DESC

Upvotes: 2

Related Questions