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.


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 $thisfirm=$_SESSION['firm_id']; ?>

Someone tell me where I am going wrong?

Gary

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.

Guido

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

Again, thank you.

Gary

You already have an (implicit) join in the query:

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?

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

Guido

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

This is the 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

To illuminate what happens with joins you might find this old post very helpful. I still have it pinned up on my wall. :wink:

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

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.

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.


$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

@SpacePhoenix Nice one, that is very memorable, I shall quote that in future too.

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.

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

thank you, thank you, thank you :smiley: :smiley:

the exact same diagrams are also in Simply SQL on pages 38-45

no, you can… but you shouldn’t :slight_smile:

I’m sure you’ve already figured it out with all the help you’ve got, but let me say it anyway :smiley: : this query works, but is not the correct way to join the two tables.
The right way with implicit join:


SELECT 
    attorney.thing1
  , attorney.thing2 
FROM law_firm , 
     attorney 
WHERE [B][COLOR="Red"]law_firm.firm_id = attorney.firm_id [/COLOR][/B]
AND law_firm.firm_id ='$thisfirm' 

The right way with INNER JOIN (preferable):


SELECT  
    attorney.thing1
  , attorney.thing2 
FROM law_firm 
INNER JOIN attorney 
[B][COLOR="Red"]ON law_firm.firm_id = attorney.firm_id [/COLOR][/B]
WHERE law_firm.firm_id ='$thisfirm' 

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)

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.



$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

it’s actually caused by you mixing ANDs and ORs

they work like addition and multiplication

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

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

:slight_smile:

also, another thing that is affecting your results is this –

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…

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

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.


attorney.firm_id = 'law_firm.firm_id'

Thank you again for taking your time to help.

Gary