Mysql join multiple tables and column repeated

Question

I know how to join multiple tables, but I tried to show a value taken from 1 column in two different columns.

So:

table1.codseller = table2.id and table1.codbuyer = table2.id

Example:

TABLE1

id_ord    | order | codseller  | codbuyer| qty           | price
----------+-------+------------+---------+---------------+---------
09518709  | 4     | 001        | 002     | 8888          | 5
09518710  | 5     | 002        | 004     | 8888          | 6
09518712  | 6     | 001        | 006     | 8888          | 9
09518713  | 7     | 001        | 003     | 8888          | 4


TABLE2

    id        | location | 
    ----------+-------+--
    001       | USA      | 
    002       | CAN      | 
    003       | GER      | 
    004       | USA      | 
    005       | GER      | 
    006       | UKN      |  



RESULT:

id_ord    | order | codseller  | codbuyer| sel_loca     | buy_loc
----------+-------+------------+---------+---------------+---------
09518709  | 4     | 001        | 002     | USA          | CAN
09518710  | 5     | 002        | 004     | CAN          | USA
09518712  | 6     | 001        | 006     | USA          | UKN
09518713  | 7     | 001        | 003     | USA          | GER

Answer 1

You can use subqueries as shown below.

SELECT 
  A.id_ord,
  A.id_ord,
  A.order,
  A.codseller,
  A.codbuyer,
 (SELECT location FROM TABLE2 B WHERE B.id=A.codseller LIMIT 1) sel_loca, -- this should work as just a single result will be returned
 (SELECT location FROM TABLE2 B WHERE B.id=A.codbuyer LIMIT 1) buy_loc
FROM TABLE1 A;

See the query run on SQL Fiddle.