Query mixing rows

**gwpaul** · Mar 23, 2011 09:59

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

**guido2004** · Mar 23, 2011 10:02

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.

**gwpaul** · Mar 23, 2011 10:06

Guido

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

Again, thank you.

Gary

**guido2004** · Mar 23, 2011 10:10

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?

**gwpaul** · Mar 23, 2011 10:21

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

**gwpaul** · Mar 23, 2011 10:36

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

**Cups** · Mar 23, 2011 10:39

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

**gwpaul** · Mar 23, 2011 10:42

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

**SpacePhoenix** · Mar 23, 2011 11:50

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.

**gwpaul** · Mar 23, 2011 13:40

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

**Cups** · Mar 23, 2011 13:44

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

**Cups** · Mar 23, 2011 13:55

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.

**gwpaul** · Mar 23, 2011 14:31

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

**r937** · Mar 23, 2011 15:10

Originally Posted by Cups

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** · Mar 23, 2011 15:12

Originally Posted by gwpaul

Does this mean you cannot SELECT * on a join?

no, you can... but you shouldn't

**guido2004** · Mar 24, 2011 04:03

Originally Posted by gwpaul

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'

**r937** · Mar 24, 2011 04:30

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)

**gwpaul** · Mar 24, 2011 09:44

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

**r937** · Mar 24, 2011 09:55

Originally Posted by gwpaul

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

**gwpaul** · Mar 24, 2011 10:10

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

**guido2004** · Mar 24, 2011 10:15

Originally Posted by gwpaul

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

**r937** · Mar 24, 2011 10:25

Originally Posted by guido2004

What you want is ...

actually, he already had it, in the ON clause

**guido2004** · Mar 24, 2011 10:27

Originally Posted by r937

actually, he already had it, in the ON clause

User Tag List

Thread: Query mixing rows

Thread Tools

Display

Query mixing rows

Bookmarks

Bookmarks

Posting Permissions