Cross Matching Results

Hi,

I have a bit of a tricky one which i cannot work out how to do.

Basically i have done the following query:

$new = new database;
$new->selects("SELECT * FROM regions WHERE country = 'Africa'");
$results = $new->getRes();
$smarty->assign('results', $results);

Code (markup):

Which will find all the different regions in Africa.

Then this has been printed out by:

{foreach from=$results item=result name=results}
           {$result.country} - (How Many Listings in this country)
           {/foreach}

Code (markup):

Then what i want to do is run another query with the 'users' table to check if any of the regions printed out match.

Does anyone know how to do this at all?

Cheers,
Adam

 

SELECT * FROM regions WHERE country = 'Africa' AND 'users' IN ( SELECT the regions )

thinks that's something you might be looking for. Just play around with the IN directive. I'm sure I have it all backwards or something..

 

Nested SQL statements will be your friend.

SELECT * FROM users WHERE region IN ( SELECT title FROM regions WHERE country = 'Africa')