MYSQL - selecting rows from tbl1 with no matching id in tbl2

[flagshipis]

Hello,

my problem in MySQL is how to select rows from a table where there is no match in a corresponding table - but there\'s a hitch!

 

The MySQL manual covers this with:

 

select table1.* from table1

LEFT JOIN table2 ON table1.id=table2.id

where table2.id is NULL;

 

my situation is a little more awkward - as if you hadn\'t guessed!

 

The detail.

Table1 contains a list of DOCUMENTS written by site members.

Table2 contains a list of DOCUMENT HITS (i.e. the userID and the docID) - this tells me who has read which documents

Table3 contains a list of USERS.

 

I need to know (for a given user) which documents they have NOT read. Using the above query doesn\'t get there.

 

A virtual king-size mars bar to all correct solutions. :-)

 

Thanks for looking in on this.

 

Victor.[/code]

[barbatruc]

First select the document_ids of the book the user read (userID = $user_id).

$book_ids = array();

$sql = "SELECT id FROM book_hits WHERE user_id = \'$user_id\'";

$result = mysql_query($sql) or die("Error selecting book ids of user");

while($book = mysql_fetch_array($result, MYSQL_ASSOC)) {

 $book_ids[] = $book[\'id\'];

}

$sql = "SELECT * FROM books";

if (!empty($book_ids)) {

 $sql .= " WHERE id NOT IN (\'". implode("\',\'", $book_ids) ."\')";

}

$books = array();

$result = mysql_query($sql) or die("Error selecting user\'s unread books");

while($book = mysql_fetch_array($result, MYSQL_ASSOC)) {

 $books[] = $book;

}

// unread books in $books!

Hope this helps.

 

JP