Returning data in one table that is not in another

bpgillett

hi. i need to retrieve the data in a field called 'scenario' for a user (identified by the field 'id') only if it is present in the table called 'cases' and not present in the table called 'completed'. both tables contain the field 'scenario', but only table 'completed' contains the 'id' field. i am using mysql v4.0.25. i can't seem to get it to work. thanks.

--brian

here's the code that i'm trying:

   $result = mysql_query("SELECT cases.scenario FROM cases WHERE cases.scenario NOT IN (SELECT completed.scenario FROM completed WHERE id ='$id')");

PopsTX

$result = mysql_query("SELECT scenario 
FROM cases
LEFT JOIN completed
WHERE cases.id = $id AND completed.scenario IS NULL ");

That should take care of things... Assuming table"completed".field "scenario" is noted in MySql as a NULL Field.

bpgillett

thanks for the suggestion, popstx. still returns error: mysql_fetch_array(): supplied argument is not a valid MySQL result resource. the 'scenario' fields in both tables are definitely set to NULL. i changed $id to '$id', but still no dice. also, the 'id' field is only in the 'completed' table. the 'cases' table only contains 1 field: 'scenario'. i also tried:

$result = mysql_query("SELECT DISTINCT completed.scenario, cases.scenario FROM completed LEFT JOIN cases ON (completed.scenario != cases.scenario) WHERE id='$id' ");

which seemed to get me a little closer....this works as long as there is only a single row in the completed table. an extra occurance of the 'scenario' is returned for each additional row in the 'completed' table. i'm pulling my hair out. any more ideas. thanks. --brian

PopsTX

//connect to and designate table in db stuff goes here

$query = "SELECT id,scenario 
FROM cases
LEFT JOIN completed
WHERE cases.id = $id AND completed.scenario IS NULL ";

$result = mysql_query($query) or die ("Error: $query. ".mysql_error());
if (mysql_num_rows($result) > 0) {
while(list( $id, $scenario ) = mysql_fetch_row($result)){

echo "$id<br />\n";
echo "$scenario<br />\n";
}
} else {
echo "<div>No results found</div>\n";
       }  
  mysql_free_result($result);
  mysql_close($dbcon);

Lars_Berg

SELECT scenario
 FROM completed
 WHERE id='$id' 
and scenario not in(
select scenario 
 from cases)

bpgillett

thanks for the suggestion, LarsBerg. unfortunatley that syntax is not supported until mysql v4.1.+(my ISP won't upgrade). PopsTX, your code returned a syntax error, but it got me on the right track. i finally used:

    SELECT cases.scenario, completed.id FROM cases LEFT JOIN completed ON  completed.scenario = cases.scenario ORDER BY cases.scenario

and test for $id later in the php with

if ($myrow["id"] != $id){

thanks again. --brian