CODEWITHSUNDEEP
mysqlsql
Muhammed
Muhammed

Reputation: 31

Select Column Twice With Different Conditions

I have two tables,Table 1 and Table 2, Table 1 has member_id,first name and last name, Table 2 has buyer_id and seller_id I will like to select first name and last name of the buyer and seller in one row

    Table_1
   member id | firstname | lastname
    1        |  John     |  Richard
    2        | Joseph    |   Curly
    3        |  Ismail   |  Jake
    4        | Elijah    |   Michael

    Table_2
  seller_id  | buyer_id|
    2        |     1   |
    3        |     4   |

I have been trying to return a table with rows like this

 seller_id | buyer_id | firstname | lastname | firstname | lastname|
     2     |   1      |    Joseph |   Curly  |  John     | Richard |

The query i used is

SELECT a.firstname,a.lastname,a.firstname,a.lastname,b.seller_id,b.buyer_id
        FROM Table_1 as a,Table_2 as b WHERE b.buyer_id = a.member_id

Upvotes: 1

Views: 138

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

You need join table_1 twice (one for join seller_id and One for join buyer_id)

SELECT 
    a.firstname
    ,a.lastname
    ,c.firstname
    ,c.lastname
    ,b.seller_id
    ,b.buyer_id
    FROM Table_2 as b
    INNER JOIN Table_1 as a ON a.member_id = b.seller_id
    INNER JOIN Table_1 as c On c.member_id = b.buyer_id

Upvotes: 1

Related Questions