Mysql – Joining three tables with null values in SQL

join;MySQL

I have a BOOKS table, a USERS table, and a RATINGS table (with a book id, a user id, and a rating value).

For each book, I would like to be able to get an array with all the users and their rating for the chosen book BUT more importantly, if the user has not rated the book, I still want it in the array but with a rating value of null, as follows :

user	book	value
1	1	3
1	2	null
2	1	null
2	2	1

And so on..

The closest way I could get to it was by doing :

SELECT u.id as user, b.id as book, r.value
        FROM users u, books b
        LEFT OUTER JOIN ratings as r ON r.book_id = b.id
        ORDER BY b.id ASC

But if I try to add LEFT OUTER JOIN ratings as r ON r.book_id = b.id AND ON r.user_id = u.id as my final clause, it obviously doesn’t work. Wrong result: it gives me the first rating found for each book, not the one related to the user.

Any idea how I could achieve this?

Best Answer

Try to use explicit join. It seems that MySQL has some issue referencing the implicit cross join.

select b.id, u.id, rating
  from books as b
   join
       users as u on 1=1
  left join ratings on u.id = userid and b.id = bookid

Related Solutions

Mysql – Storing vs calculating aggregate values

It depends. Pre-calculating aggregate values places a larger load on writes, deriving them makes reads more difficult

If you are frequently accessing a derived value, pre-calculation is a valid de-normalization step. However, in this instance, I recommend using a Materialized View (a view, written to disk, linked by trigger to the parent tables). The materialized view is designed to store frequently asked but tedious-to-derive data, and is useful for high numbers of writes and low numbers of reads.

In a high-write, high-read scenario, consider having a task in the background which mimics the effects of a materialized view, but in less than real-time. This will present a "good enough" average while preserving write and read performance.

In no circumstances, should you treat the derived column like a "normal" column: make sure the data presented in the Widgets "view" is present elsewhere in the table, such that the entire tuple can be derived by whatever processes you emplace. This question is also strongly database (and database-version) specific, so I recommend performance testing of the aggregate (with appropriate indexes) against a normal-sized data set and the materialized view.

MySQL: LEFT OUTER JOIN within reason

First consider a query that computes which rows are actually relevant from tablethree. With the assumption that with "most recently entered result" you mean "most recent enddate" the following query would gather the appropriate rows:

SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid

Now you can build a join to retrieve not only sid, but all of the data of tablethree:

SELECT a.*
FROM tablethree a
INNER JOIN (
  SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
) b
ON a.sid = b.sid AND a.enddate = b.enddate

This is the result set you actually want to "left join in". You have to insert this into your original query:

SELECT t1.*
FROM tableone AS t1
INNER JOIN tabletwo AS t2
  ON t1.cid = t2.id
LEFT OUTER JOIN (
  SELECT a.*
  FROM tablethree a
  INNER JOIN (
    SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
  ) b
  ON a.sid = b.sid AND a.enddate = b.enddate
) AS t3
  ON t3.sid = t2.sid
WHERE t1.fieldone = 1 
  AND t1.odate NOT BETWEEN t3.startdate AND t3.enddate

What should also work is the following:

SELECT t1.*
FROM tableone AS t1
INNER JOIN tabletwo AS t2
  ON t1.cid = t2.id
LEFT OUTER JOIN tablethree AS t3
  ON t3.sid = t2.sid
LEFT OUTER JOIN (
  SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
) mostrecent
  ON t3.sid = mostrecent.sid AND t3.enddate = mostrecent.enddate

WHERE t1.fieldone = 1 
  AND t1.odate NOT BETWEEN t3.startdate AND t3.enddate
  AND mostrecent.enddate IS NULL

This includes both tablethree and the new SELECT as left joins, and sorts out the rows where mostrecent.enddate IS NULL (meaning those rows which are actually not most recent). This should lead to the same result, but MySQL may be able to compute this result a little faster. EXPLAIN on both queries should reveal possible differences in computation.

Best Answer

Related Solutions

Mysql – Storing vs calculating aggregate values

MySQL: LEFT OUTER JOIN within reason

Related Question