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?

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php