CODEWITHSUNDEEP
sqljoinselect
RandomGuy
RandomGuy

Reputation: 3

How to get following data via SQL

I was struggling on how to get the following data.

I have 2 tables:

 |  Company Name  | Customer ID 1 | Customer ID 2 | Customer ID 3
 +----------------+---------------+---------------+-----------------
 |    Android     |       1       |       2       |      3
 |      IOS       |       4       |       5       |      6

And the following table

 |  Customer ID | Customer Name |
 +--------------+---------------+
 |      1       |     Edwin     |     
 |      2       |     Stanley   |       
 |      3       |     Roward    |        
 |      4       |      Kim      |      
 |      5       |     Flare     |       
 |      6       |     Queen     |

How can I get the result such as this in SQL query?

 |  Company Name  | Customer Name1 | Customer Name2 | Customer Name3
 +----------------+----------------+----------------+---------------
 |    Android     |     Edwin      |    Stanley     |      Roward
 |      IOS       |       Kim      |     Flare      |      Queen

Upvotes: 0

Views: 23

Answers (1)

GMB
GMB

Reputation: 222482

You can join, join ... and join:

select
    t.company_name,
    c1.customer_name customer_name1,
    c2.customer_name customer_name2,
    c3.customer_name customer_name3
from mytable t
inner join customers c1 on c1.customer_id = t.customer_id1
inner join customers c2 on c2.customer_id = t.customer_id2
inner join customers c3 on c3.customer_id = t.customer_id3

An important thing here is to use different table aliases for each join to avoid conflicts on the table that is joined multiple times.

Upvotes: 1

Related Questions