CODEWITHSUNDEEP
mysqlsqlleft-join
theo
theo

Reputation: 299

SQL-Joining two tables on Column values=Column Name

I am getting a real problem with joining two tables.

I have this main table:

----------------
|ID|Stock|Group|
|--+-----+-----|
|K3|US 11|1    |
|K3|US 23|2    |
|K3|HK 10|2    |
|G5|SG 56|1    |
|D1|PH 12|1    |
|D1|UK 23|2    |
----------------

and this mapping table:

------------------------------------
|ID|Local1|Local2|Foreign1|Foreign2|
|--+------+------+--------+--------|
|K3|10    |20    |25      |30      |
|G5|20    |30    |35      |40      |
|D1|10    |15    |20      |50      |
------------------------------------

How can I map the combination of Stock and Group values to the column of my mapping table? For which US=Local and *others*=Foreign. For example, the stock US 11 of K3 which is in Group 1. US 11 means the Market is Local. How can I get the value of the Column Local1 from the mapping table? Then join the tables as the result like this:

----------------------
|ID|Stock|Group|Ratio|
|--+-----+-----+-----|
|K3|US 11|1    |10   |
|K3|US 23|2    |20   |
|K3|HK 10|2    |30   |
----------------------

I haven't tried anything because I have no idea how to do it. Please help.

Upvotes: 1

Views: 152

Answers (3)

Steve Lovell
Steve Lovell

Reputation: 2564

If i understand correctly, the following should be what you need:

Select
    m.ID,
    m.Stock,
    m.[Group],
    Case
        when left(m.stock,2)='US' and m.[Group] =1 then mapping.Local1
        when left(m.stock,2)='US' and m.[Group] =2 then mapping.Local2
        when m.[Group]=1 then mapping.Foreign1
        Else mapping.Foreign2
    End as Ratio
From
    Main m
    Join mapping on main.id = mapping.id

Upvotes: 4

ScaisEdge
ScaisEdge

Reputation: 133370

you could use a specific select case when for set the map you really need (but is a dedicated solution)

  select a.id,a.Stock, a.Group,
      case  when  stock ='US 11' and group = 1 then b.Local1
            when  stock ='US 23' and group = 2 then b.Local2
            when  stock ='HK 10' and group = 2 then b.Foreign2
            .....
  from main as a
  inner join mapping as b on a.id=b.id

Upvotes: 1

Jenish
Jenish

Reputation: 535

You have join table using foreign key

select main.id, main.stock, main.group, create_ratio_here from main left join mapping on mapping.id = main.id where main.id = k3

Upvotes: 1

Related Questions