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

    Join Date
    Aug 2012
    Posts
    10
    Rep Power
    0

    Which mysql join will be applicable?


    Hi there,
    As I always get confused of mysql join. Here are my descriptions:
    I have two tables

    Table: price
    ===========================
    ID || company || volume || price
    ----------------------------------
    01 || comp-A || 300000 || 500
    ----------------------------------
    02 || comp-B || 260000 || 400
    ---------------------------------
    03 || comp-C || 300000 || 450
    ---------------------------------
    04 || comp-D || 280000 || 500
    --------------------------------
    05 || comp-E || 275000 || 400
    --------------------------------
    06 || comp-F || 290000 || 450
    =========================

    Table: type_of_company
    ===============================
    ID || company || comp_type || country
    ---------------------------------------
    01 || comp-A || Transport || Canada
    ---------------------------------------
    02 || comp-B || Oil & Lubri || Brazil
    ---------------------------------------
    03 || comp-C || Transport || India
    ---------------------------------------
    04 || comp-D || Transport || USA
    --------------------------------------
    05 || comp-E || Courier || Bangladesh
    --------------------------------------
    06 || comp-F || Transport || Jermany
    ===============================

    user of my website will check which company are of 'Transport' type and their share volume and price. They will select company type from a drop-down menu and after pressing 'SUBMIT' menu, they will see this page:

    Type of company: Transport
    ======================
    Company || Price || Volume
    ======================
    comp-A || 500 || 300000
    ----------------------------
    comp-C || 450 || 300000
    ----------------------------
    comp-D || 500 || 280000
    ----------------------------
    comp-F || 450 || 290000
    ======================


    which mysql join/query should use in this case?

    Expecting a help from all expert member.

    Regards
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    Originally Posted by infomamun
    which mysql join/query should use in this case?
    INNER JOIN

    Comments on this post

    • infomamun agrees : Very helpful
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    10
    Rep Power
    0
    I am unable to build a successful mysql query in this case. It will be my pleasure if you show me the sample query for the above tables, assuming db1 is the database for price table and db2 is the database for type_of_company table.

    I have built following like query:
    PHP Code:
    mysql_connect("localhost""username""password") or die(mysql_error());
    $sql "SELECT db1.price.company, db1.price.price, db1.price.volume FROM db1.price INNER JOIN db2.type_of_company ON db1.price.company=db2.type_of_company.company WHERE db2.type_of_company.comp_type='Transport'";
    $result mysql_query($sql);
    $rows_number mysql_num_rows($result);
    echo 
    $rows_number
    But showing following error:
    Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/...../pagename.php on line 4
    Please note that both db1 and db2 are on the same server.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    query looks okay

    run it outside of php, directly in mysql, to see the real error message
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    10
    Rep Power
    0
    I solved it.
    There was a little error at table name in my actual code. I missed a letter from that table name.
    Thanks r937 for your help.
    Would you tell me all other codes, quotation marks are right in my php code (although it is working). Like can I omit database name before table name from some place of that code?
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    Originally Posted by infomamun
    Would you tell me all other codes, quotation marks are right in my php code (although it is working).
    sorry, not me, i don't do php (this is the mysql forum)

    Originally Posted by infomamun
    Like can I omit database name before table name from some place of that code?
    yes, you can simplify the query by using table aliases
    Code:
    SELECT p.company
         , p.price
         , p.volume 
      FROM db2.type_of_company AS t 
    INNER 
      JOIN db1.price AS p  
        ON p.company = t.company 
     WHERE t.comp_type = 'Transport'

    Comments on this post

    • infomamun agrees : Very helpful
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    10
    Rep Power
    0
    Thank you very much.

IMN logo majestic logo threadwatch logo seochat tools logo