#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Posts
    6
    Rep Power
    0

    Combine a fields in different tables


    I need to display in a table format the contents of 2 tables so that I have the following info.

    (Table Headers)
    Date Make Model Mileage Color Run Num Sold KBB Retail Street

    I can make this work for every thing except Sold.

    $db = mysql_connect($host, $username, $password);
    mysql_select_db($database);

    $sql = 'SELECT distinct *
    FROM Watch
    ORDER by Run_Num';


    mysql_select_db('Watch');
    $retval = mysql_query( $sql, $db );
    $count = mysql_num_rows($retval);

    Then I have to make a new section that gives me the Sold info.

    $sql1 = 'SELECT distinct b.Year, b.Make, b.Model, b.Mileage, b.Color, b.Price, b.Date, b.Link
    FROM Watch a, Sold b
    WHERE a.Year = b.Year and a.Make = b.Make and a.Model = b.Model
    ORDER by b.Make, b.Model, b.Year';

    mysql_select_db('Sold');
    $retval1 = mysql_query( $sql1);
    $count1 = mysql_num_rows($retval1);

    Question how can I make it one sql statement to do everything. I do not know how to use joins and need expert help as I am new to MySQL.

    Thanks in advance
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by gw32
    I do not know how to use joins
    actually, you do

    what you wrote ~is~ a join --
    Code:
            FROM Watch a, Sold b
            WHERE a.Year = b.Year and a.Make = b.Make and a.Model = b.Model
    this is an inner join, the so-called "comma join" because the tables to be joined are in a comma-delimited list in the FROM clause, with the join conditions in the WHERE clause (where they are all lumped together, along with filter conditions)

    it is now advised to use only explicity JOIN syntax

    for example, --
    Code:
      FROM Watch AS a
    INNER
      JOIN Sold AS b
        ON b.Year = a.Year 
       AND b.Make = a.Make 
       AND b.Model = a.Model
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Posts
    6
    Rep Power
    0

    Tried it.


    Originally Posted by r937
    actually, you do

    what you wrote ~is~ a join --
    Code:
            FROM Watch a, Sold b
            WHERE a.Year = b.Year and a.Make = b.Make and a.Model = b.Model
    this is an inner join, the so-called "comma join" because the tables to be joined are in a comma-delimited list in the FROM clause, with the join conditions in the WHERE clause (where they are all lumped together, along with filter conditions)

    it is now advised to use only explicity JOIN syntax

    for example, --
    Code:
      FROM Watch AS a
    INNER
      JOIN Sold AS b
        ON b.Year = a.Year 
       AND b.Make = a.Make 
       AND b.Model = a.Model
    I put your code under the select statement:
    $sql = 'SELECT distinct b.Year, b.Make, b.Model, b.Mileage, b.Color, b.Price, b.Date, b.Link
    FROM Watch AS a
    INNER
    JOIN Sold AS b
    ON b.Year = a.Year
    AND b.Make = a.Make
    AND b.Model = a.Model
    ORDER by b.Make, b.Model, b.Year';

    Now I only get the vehicle that was previously sold and not all vehicles with their values and a price in the sold column or a blank.

    Please go here and see what I have and it'll look better (http://glwdev.net/GDAA/watch.php).
    All I want is the "Sold For" value from table 2 inserted into the first table where year, make, and model and the links.

    Thanks
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    first of all, i doubt you want anything "inserted into" the watch table

    can you please explain why the two 2002 ford explorers have different mileages in the watch table than the mileage of the ford explorer in the sold table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    first of all, i doubt you want anything "inserted into" the watch table

    can you please explain why the two 2002 ford explorers have different mileages in the watch table than the mileage of the ford explorer in the sold table?
    First there are 2 different vehicles one blue and one silver.
    The one in the sold table is a reference for the guys as to what we want to pay for the same basic vehicle.

    Also I used the wrong context for insert. It should have read inserted into the displayed table in the Sold For column.

    I'll gladly send you the code if needed.

    Thanks
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by gw32
    It should have read inserted into the displayed table in the Sold For column.

    I'll gladly send you the code if needed.
    sorry, i can't use any more code

    your query references a column in the Sold table called "price"

    are you sure that's what it's called?

    also, here's a very important point -- have you tested your query outside of php, i.e. directly in mysql?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    sorry, i can't use any more code

    your query references a column in the Sold table called "price"

    are you sure that's what it's called?

    also, here's a very important point -- have you tested your query outside of php, i.e. directly in mysql?
    Price is correct.
    I'll try outside php later today.

    Thanks
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Posts
    6
    Rep Power
    0
    Originally Posted by gw32
    Price is correct.
    I'll try outside php later today.

    Thanks
    OK, got it to display the Sold for value like I wanted by doing this:

    $sql = 'SELECT distinct a.*, b.Price
    FROM Watch AS a
    INNER JOIN Sold AS b
    ON b.Year = a.Year
    AND b.Make = a.Make
    AND b.Model = a.Model
    ORDER by Make';

    Also changed the "Sold For" to "Bought For".

    But it only shows the vehicles that have a "Bought" value. How do I get the rest to display that have a "0" value?

    Today I have 10 more that need to be displayed.

    Thanks
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    seems like you've completely changed the intent of the query by switching from displaying all the Sold columns to displaying all the Watch columns

    note that you no longer need DISTINCT, since you're gonna get all the individual ford explorers anyway

    i'm not sure what you mean by displaying the cars that have a "0" price -- surely there aren't any cars that were actually given away?

    or perhaps you just want to list the Watch cars, and identify which ones were sold?

    in that case, use a LEFT OUTER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo