[SOLVED] select statement that does the same as this

[scarhand]

looking for a select statement that can replace this:

 

<?php

$sql = mysql_query("SELECT * FROM topics");

while ($row = mysql_fetch_array($sql))
{
  $id = $row['id'];
  
  $readcount = mysql_num_rows(mysql_query("SELECT * FROM readtopics where topicid='$id'"));

  if ($readcount != 0)
  {
    echo "this topic has been read";
  {
}

?>

 

any help would be greatly appreciated

[teng84]

SELECT count(topicid) FROM readtopics where topicid in (SELECT id FROM topics) group by topicid

[scarhand]

SELECT count(topicid) FROM readtopics where topicid in (SELECT id FROM topics) group by topicid

 

im trying to select stories that have not been read...

 

i have a table called "stories" and a table called "stories_read"

 

i tried this, but it does not work. i am not sure what the syntax for "NOT IN" is, i tried "!IN" but that does not work

 

heres what ive got:

 

the $myid variable is already declared as an integer

 

"SELECT * FROM stories WHERE id !IN (SELECT storyid FROM stories_read) AND readerid='$myid'"

 

might give you a better idea of what im trying to do here.

 

"readerid" is in the stories_read table.

[todding01]

Maybe something like

 

SELECT * 
FROM stories_read
WHERE stories.id <> stories_read.id

[scarhand]

i figured it out, there was an "NOT IN" syntax all along....wow...i feel dumb.

 

here is my solution:

 

"SELECT * FROM stories WHERE id NOT IN (SELECT storyid FROM stories_read WHERE readerid='$myid')"

[fenway]

I think a LEFT JOIN... IS NULL would be better...