Jump to content


Photo

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


  • Please log in to reply
1 reply to this topic

#1 flagshipis

flagshipis
  • New Members
  • Pip
  • Newbie
  • 1 posts
  • LocationHampshire, UK.

Posted 23 April 2003 - 11:52 AM

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]

#2 barbatruc

barbatruc
  • Members
  • PipPip
  • Member
  • 28 posts
  • LocationMontreal, Quebec, Canada

Posted 30 April 2003 - 04:43 PM

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
Unfortunately, PHP \'empowered every moron with a copy of Windows notepad to be \"web programmers\". (...) Give PHP a real INFRASTRUCTURE. Use PEAR!!!\'




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users