how to query 2 tables for matching fields

[cut71]

Hi, basically I have 2 tables in a database, 'notBuilt' and 'built'. The column names are similar being 'model', 'location', 'price', and 'features' in each table. I have a query for the 'notBuilt' table,arrayed, and echo out and everything works fine. What I would like to do is add a second query to search if there is a built model available at a location. So if both the 'model' and 'location' match in both tables, I can add an if query match then available = yes, else no. I have tried a number of variations, but cant seem to get it correct.
Thanks

[james438]

Try this:


SELECT built.model, built.location, notBuilt.location, notBuilt.model FROM built, notBuilt
WHERE built.model='SL' AND built.location='Tennessee' AND
notBuilt.location='Tennessee' AND notBuilt.model='SL'

[cut71]

Thanks James, that worked! I was actually able to simplify this query since it was placed within the one I was already running ($model and $ community are results from searching the notBuilt table):

<?php
//Search notBuilt table
$notBuiltSQL = mysql_query("SELECT * FROM notBuilt WHERE ...");
$i = 0 ;
while($Rows = mysql_fetch_array($notBuiltSQL)) {
$model = $Rows['model'];
$community= $Rows['community'];
$price= $Rows['price'];
$features= $Rows['features'];
//search Built table for matching results
$compareSQL = mysql_query("SELECT * FROM Built WHERE model = '$model' AND community = '$community' ;");
$compareResults = mysql_num_rows($compareSQL);
if ($compareResults !== 0) {
$avail = "yes" ; }
else { avail = "no" ; }
//echo results table
echo "....";
$i++;
}
mysql_close();
?>

[james438]

np, glad I could help. If you want you can edit your original post and select the advanced settings and mark this topic resolved.