Re: joints - extracting common results from comparing 2 tables

On 10/23/2013 8:15 AM, Taco Mathijs Hillenaar-Meerveld wrote:

Good afternoon, i haven't done much with php and Mysql the past 2 years so my knowledge is not really up-to-date. i'm not a hero when it comes to math. actually, it's a weakness but trying to fix that. i made a query where i compare 2 tables against eachother and then i want to see a table that shows me results: the Relation + Surname should match. i have chosen for those two because Surname was a required input and all contacts must have a relation. also, in those tables there are fields that have completely messed up information that shouldn't be there in the first place, phone numbers as names f.e.. that aside i made this query:

     $sql = 'SELECT table_a1.Surname, table_a2.Relation, table_a1.City
     FROM table_a1
     INNER JOIN table_a2
     ON table_a1.Relation=table_a2.Relation';w

I get a row back. my question: is this an appropiate and common way to get this job done? im NOT used to dig deep in this kind of stuff but i can see in the future i will need a few handy scripts. tips and advice are very welcome. You really don't need the INNER JOIN to do this. Your task ( to find those records that have matching relation values can be done very simply. Replace it all with :

"select a1.Surname, a1.City, a2.Relation

    FROM table_a1  a1, table_a2 a2
      where a1.Relation = a2.Relation"

(Note my use of the simpler alias instead of the full table name.) I realize that this is probably a simplification of your true goal, but just in case it is not, I point out that I don't see the value of this particular query. You are looking for matching records but you don't pull any information from the matched records that you don't already have in the a1 table. Do I assume that you will be adding more fields in your selection?