help with sub query or join

[caroline_162]

Hello, i need some help with these queries please.

 

Ive got 2 queries which work fine on their own but i need to use the results from the first as input to the second.

 

So the result being $client_ref. i then need to use the result in the second query.

 

Any help is appreciated .

 

ATM client ref is coming out correctly in NEW1, 2,and 4 but not 3. For some reason the value is not getting inside the query. If i define client_ref (currently commented out) then it works perfectly but i need them to work together ! thanks !

 

$client_ID=38 ;



$SQL1= "SELECT client_ref FROM client WHERE client_ID=$client_ID IN ";

$result = mysql_query($SQL1);


while ($db_field = mysql_fetch_assoc($result)) {
print $client_ID=$db_field['client_ID'] . "<BR>" ;
$client_ref=$db_field['client_ref']. "<BR>" ;
print "NEW 1: " . $client_ref;
}


print "NEW 2: " . $client_ref . "<BR>" . "<BR>" . "<BR>";



//$client_ref=CAR;



$SQL2 = "SELECT case_number FROM case1 WHERE case_number LIKE '%$client_ref%'";

$result2 = mysql_query($SQL2);


while ($db_field = mysql_fetch_assoc($result2)) {
print "NEW 3: " . $client_ref;
print "<BR>". "CN:" . $db_field['case_number'] . "<BR>";

}

print "<BR>"."<BR>"."<BR>"."NEW 4: " . $client_ref;

[Christian F.]

For something like this you'll want to use a JOIN, INNER JOIN as the case might be.

[Barand]

I think you need something like this

 

SELECT client.client_ref, case1.case_number
FROM case 1
  INNER JOIN client ON INSTR(case1.case_number, client.client_ref) <> 0
WHERE client.client_ID = $clientid