SitePoint Sponsor

User Tag List

Results 1 to 23 of 23
  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,404
    Mentioned
    149 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,404
    Mentioned
    149 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
    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.

  8. #8
    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

  9. #9
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,909
    Mentioned
    95 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

  10. #10
    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

  11. #11
    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.

  12. #12
    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

  13. #13
    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

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,020
    Mentioned
    53 Post(s)
    Tagged
    2 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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,020
    Mentioned
    53 Post(s)
    Tagged
    2 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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,404
    Mentioned
    149 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'

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,020
    Mentioned
    53 Post(s)
    Tagged
    2 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)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just when I think I have it, a curve comes in.

    Parts of the query work, other parts do not.

    I have the query to select where one of the "county columns" in law_firm contains the name of a county, that works.

    However, it does not filter the state, so if there are more than one "chester counties" in the states, they all show up.

    I also have a filter of status, that does not work.

    It would appear that my AND statements are not working or correct.

    PHP Code:

    $query_profile 
    "SELECT attorney.fname, attorney.initial, 
    attorney.lname,attorney.email, attorney.att_image, attorney.lawyer_description, 
    law_firm.firm_name, law_firm.street, 
    law_firm.suite, law_firm.city, 
    law_firm.state, law_firm.zip,law_firm.phone,
    law_firm.fax, law_firm.url, law_firm.description,
    law_firm.realestate, law_firm.business, law_firm.criminal,law_firm.bankruptcy, law_firm.family_law,
     law_firm.labor, law_firm.estate, law_firm.pi, law_firm.general 
    FROM law_firm LEFT JOIN attorney ON law_firm.firm_id  = attorney.firm_id 
    WHERE 
    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 law_firm.state = '$state
    AND attorney.firm_id = 'law_firm.firm_id' AND law_firm.status = '2' "

    Thanks again for all the help.

    Gary

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,020
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by gwpaul View Post
    It would appear that my AND statements are not working or correct.
    it's actually caused by you mixing ANDs and ORs

    they work like addition and multiplication

    for example, what is 1+2*3 ? is it 1+(2*3) or (1+2)*3 ?

    to mix ANDs and ORs, always use parentheses to ensure you get exactly the logic you want



    also, another thing that is affecting your results is this --
    Code:
    attorney.firm_id = 'law_firm.firm_id'
    the attorney's firm_id is never going to be equal to that character string

    let's revise the query to avoid the ORs problem...
    Code:
    SELECT attorney.fname
         , attorney.initial
         , attorney.lname
         , attorney.email
         , attorney.att_image
         , attorney.lawyer_description
         , law_firm.firm_name
         , law_firm.street
         , law_firm.suite
         , law_firm.city
         , law_firm.state
         , law_firm.zip
         , law_firm.phone
         , law_firm.fax
         , law_firm.url
         , law_firm.description
         , law_firm.realestate
         , law_firm.business
         , law_firm.criminal
         , law_firm.bankruptcy
         , law_firm.family_law
         , law_firm.labor
         , law_firm.estate
         , law_firm.pi
         , law_firm.general 
      FROM law_firm 
    LEFT 
      JOIN attorney 
        ON attorney.firm_id = law_firm.firm_id
     WHERE '$_POST[county]' IN ( law_firm.county_0
                               , law_firm.county_1
                               , law_firm.county_2
                               , law_firm.county_3
                               , law_firm.county_4 )
       AND law_firm.state = '$state' 
       AND law_firm.status = '2'
    finally, you have a repeating group in the law_firm table, those 5 county columns -- these should be normalized into a separate table because as they are now, they will slow down this query
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

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

    Thank you for your help, it works perfect.

    This should always match since it is the foreign key, I was concerned about redundancy. If it does not match then I suspect I have a big problem on my hands.

    PHP Code:
    attorney.firm_id 'law_firm.firm_id' 
    Thank you again for taking your time to help.

    Gary

  21. #21
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,404
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by gwpaul View Post
    This should always match ...
    PHP Code:
    attorney.firm_id 'law_firm.firm_id' 
    This never matches. You aren't confronting two columns, but a column and a string.
    What you want is
    Code:
    attorney.firm_id = law_firm.firm_id
    without the quotes

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,020
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by guido2004 View Post
    What you want is ...
    actually, he already had it, in the ON clause

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,404
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    actually, he already had it, in the ON clause



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
  •