Advanced Search

Results 1 to 4 of 4

Thread: how to query 2 tables for matching fields

  1. #1
    Join Date
    Oct 2009
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Default how to query 2 tables for matching fields

    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

  2. #2
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,681
    Thanks
    80
    Thanked 89 Times in 87 Posts

    Default

    Try this:

    Code:
    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'
    To choose the lesser of two evils is still to choose evil. My personal site

  3. The Following User Says Thank You to james438 For This Useful Post:

    cut71 (04-21-2011)

  4. #3
    Join Date
    Oct 2009
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Default

    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):
    Code:
    <?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();
    ?>

  5. #4
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,681
    Thanks
    80
    Thanked 89 Times in 87 Posts

    Default

    np, glad I could help. If you want you can edit your original post and select the advanced settings and mark this topic resolved.
    To choose the lesser of two evils is still to choose evil. My personal site

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •