SitePoint Sponsor

User Tag List

Results 1 to 23 of 23

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query mixing rows

    I have a query that is calling data from the wrong row.

    If I create a new row and do not fill information in some of the rows, it brings the information from a different row that has a different ai unique id.

    This is the query.

    PHP Code:
    mysql_select_db($database_assess$assess_remote);
    $query_profile "SELECT * FROM law_firm , attorney WHERE law_firm.firm_id ='$thisfirm'";
    $query_limit_profile sprintf("%s LIMIT %d, %d"$query_profile$startRow_profile$maxRows_profile);
    $profile mysql_query($query_limit_profile$assess_remote) or die(mysql_error());
    $row_profile mysql_fetch_assoc($profile); 
    This is the session variable I am using.
    PHP Code:
    <?php $thisfirm=$_SESSION['firm_id']; ?>
    Someone tell me where I am going wrong?

    Gary

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    You are joining two tables (law_firm and attorney) without specifying the join conditions. That means the result will be a cross join : all rows from law_firm will be joined with all rows from attorney.

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Guido

    Thank you very much for your quick reply. So I insert a join into the query?

    Again, thank you.

    Gary

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    You already have an (implicit) join in the query:
    Code:
    FROM law_firm , attorney
    That comma means a join between the two tables, law_firm and attorney.
    What is missing is the join condition. In the WHERE clause you should add a line that says what columns to use to join. What is the logical connection between the two tables? How do you know which attorney works for what firm?

  5. #5
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I use a foreign key (innodb) to link the two tables.

    I also have a 4 field form that inserts 3 fields into law_firm and 1 into attorney.

    Gary

  6. #6
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Guido

    Thank you again for your help, I believe the issue is solved with your kind guidance.

    This is the code.

    PHP Code:
    $query_profile "SELECT * FROM law_firm , attorney WHERE law_firm.firm_id ='$thisfirm' AND attorney.firm_id ='$thisfirm' "
    Please let me know if you feel there is a better way.

    Again, thank you!

    Gary

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by gwpaul View Post
    Guido

    Thank you again for your help, I believe the issue is solved with your kind guidance.

    This is the code.

    PHP Code:
    $query_profile "SELECT * FROM law_firm , attorney WHERE law_firm.firm_id ='$thisfirm' AND attorney.firm_id ='$thisfirm' "
    Please let me know if you feel there is a better way.

    Again, thank you!

    Gary
    I'm sure you've already figured it out with all the help you've got, but let me say it anyway : this query works, but is not the correct way to join the two tables.
    The right way with implicit join:
    Code:
    SELECT 
        attorney.thing1
      , attorney.thing2 
    FROM law_firm , 
         attorney 
    WHERE law_firm.firm_id = attorney.firm_id 
    AND law_firm.firm_id ='$thisfirm'
    The right way with INNER JOIN (preferable):
    Code:
    SELECT  
        attorney.thing1
      , attorney.thing2 
    FROM law_firm 
    INNER JOIN attorney 
    ON law_firm.firm_id = attorney.firm_id 
    WHERE law_firm.firm_id ='$thisfirm'

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    WHERE law_firm.firm_id = '$thisfirm' works, but only in mysql, which silently performs an implicit conversion

    WHERE law_firm.firm_id = $thisfirm is much preferred

    (assuming firm_id is a numeric column, e.g. integer)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    To illuminate what happens with joins you might find this old post very helpful. I still have it pinned up on my wall.

  10. #10
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm afraid my walls are not big enough to keep handy all the information I am lacking...

    Thank you for reply and the link!

    Gary

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Cups View Post
    To illuminate what happens with joins you might find this old post very helpful. I still have it pinned up on my wall.
    thank you, thank you, thank you

    the exact same diagrams are also in Simply SQL on pages 38-45
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,014
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Just to add to the link to an old forum post (post #7 by Cups), there is this one which visually shows using venn diagrams.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  13. #13
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So the original page/query is working fine, however on a different page I need to expand the query.

    I need to data from both law_firm and attorney, however I am still getting results from all rows. There is only one row with the status of 2, but I get all the rows.

    If I remove attorney, from the query, it works fine, but does not get me the information from the attorney table.



    PHP Code:
    $query_profile "SELECT * FROM attorney, law_firm WHERE law_firm.status = '2' AND law_firm.county_0 ='$_POST[county]' or  law_firm.county_1 ='$_POST[county]' or law_firm.county_2 ='$_POST[county]' or  law_firm.county_3 ='$_POST[county]' or  law_firm.county_4 ='$_POST[county]' AND state = '$_POST[state]' "
    Does it make more sense to create a seperate query for the attorney table? If so, how would I reference the original query?

    Thank you again.

    gary

  14. #14
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    @SpacePhoenix Nice one, that is very memorable, I shall quote that in future too.

  15. #15
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Code:
    SELECT attorney.thing1, attorney.thing2 
    FROM law_firm
    LEFT JOIN attorney
    ON law_firm.firm_id  = attorney.law_firm
    WHERE 
    law_firm.firm_id = 2
    AND
    (other where clauses here)
    Make the join implicit using the fuller syntax and it should work, though I am shaky when it comes to so many OR clauses.

    If it does not - then consider posting some or all of your table schema* and say, 2 or 3 rows which typify your problem along with the result you want.

    * submit DESCRIBE law_firm into your database as an sql query to get this.
    Last edited by Cups; Mar 23, 2011 at 13:57. Reason: Got the join wrong way round, first should be the table from which the WHERE is done

  16. #16
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cups

    It looks like I will be able to get it to work using your advice and example.

    Does this mean you cannot SELECT * on a join?

    Thank you for your help.

    Gary

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by gwpaul View Post
    Does this mean you cannot SELECT * on a join?
    no, you can... but you shouldn't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •