Jump to content


This topic is now archived and is closed to further replies.


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

Recommended Posts


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.



Share this post

Link to post
Share on other sites

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.



Share this post

Link to post
Share on other sites


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.