[SOLVED] multiple select

[test123]

(mysql 5.0.27 on debian)

 

I have 3 simple tables as follows:

 

table 'user'

+---+------+
|ID | name |
+---+------+
| 1 | joe  |
+---+------+
| 2 | bob  |
+---+------+

table 'book'

+----+-------+
| ID | title |
+----+-------+
|  1 | test1 |
+----+-------+
|  2 | test2 |
+----+-------+
|  3 | test3 |
+----+-------+
|  4 | test4 |
+----+-------+
|  5 | test5 |
+----+-------+

table 'book_read'

+---------+---------+
| user_id | book_id |
+---------+---------+
|    1    |    1    |
+---------+---------+
|    1    |    2    |
+---------+---------+
|    2    |    1    |
+---------+---------+
|    2    |    3    |
+---------+---------+
|    2    |    4    |
+---------+---------+

 

I would like to select a list of all books for a specific user with an additional field for read/not read (1/0 or yes/no)

 

An example for user bob:

 

+-------+------+
| book  | read |
+-------+------+
| test1 |  1   |
+-------+------+
| test2 |  0   |
+-------+------+
| test3 |  1   |
+-------+------+
| test4 |  1   |
+-------+------+
| test5 |  0   |
+-------+------+

 

I have tried:

 

SELECT book.title, COALESCE(COUNT(book_read.book_id), 0) AS read
FROM book
LEFT OUTER JOIN book_read ON book_read.book_id = book.id
GROUP BY book.id

 

The problem is that it shows all books read/not read (not specific to a user)

 

The other thing I tried is:

 

SELECT book.title, COALESCE(COUNT(book_read.book_id), 0) AS read
FROM book
LEFT OUTER JOIN book_read ON book_read.book_id = book.id
WHERE book_read.user_id IN (
    SELECT user_id FROM book_read WHERE user_id = 2)
GROUP BY book.id

 

This works well, but I don't have the empty line for books not read by this person. (read = 0)

 

Any idea how that could be achieved? With CASE maybe?

[GingerRobot]

Well i guessed it was going to be a sub query, and after a bit of playing around, i came up with:

 

SELECT book.title, book.id AS bookid, (

SELECT count( * ) 
FROM book_read
WHERE book_read.user_id =2
AND book_read.book_id = bookid
) AS `read` 
FROM book

 

With the data you had, it produces:

 

title  bookid  read  
test1 1 1 
test2 2 0 
test3 3 1 
test4 4 1 
test5 5 0 

[test123]

excellent, thank you!