CODEWITHSUNDEEP
mysqlsql
Khant Thu Linn
Khant Thu Linn

Reputation: 6133

SQL: Select Different column depending on other table column value

I am quite new in SQL. Currently, I wrote like this to select different column depending on other table column value.

select 
IF((SELECT COUNT(*) FROM Language WHERE PSID=53435 and Language='zg')>0,
AddressZG,Address) as Address
 from Classes where ID = 1

I need to write multiple checking in that (Language='uni') and Language='en')). How can I write?

Currently, I wrote like this to support multiple if-else but look like it is not correct way.

select 
IF((SELECT COUNT(*) FROM Language WHERE PSID=3593031830770623 and Language='zg')>0,
AddressZG,(select 
IF((SELECT COUNT(*) FROM Language WHERE PSID=3593031830770623 and Language='uni')>0,
Address,Address) as Address
 from Classes where ID = 1)) as Address
 from Classes where ID = 1

How shall I write multiple if-else to select different column depending on other table column value?

Upvotes: 1

Views: 443

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I think you can do what you want more directly using join and group by. I think the logic you want is:

select coalesce(max( case when language = 'zg' then c.addresszg end),
                max( case when language = 'uni' then c.addressuni end),
                max(address)
               )
from classes c left join
     Language l
     on l.PSID = 53435
where c.id = 1

Upvotes: 1

JuSun Lee
JuSun Lee

Reputation: 144

COUNT -> EXISTS

SELECT
  CASE WHEN EXISTS(SELECT 1 FROM Language WHERE PSID=3593031830770623 and Language='zg') THEN AdressZG
       WHEN EXISTS(SELECT 1 FROM Language WHERE PSID=3593031830770623 and Language='uni') THEN Address
       ELSE Address
  END AS Address
FROM Classes WHERE ID = 1

Upvotes: 0

Sebastian Brosch
Sebastian Brosch

Reputation: 43564

You can use CASE instead of IF:

SELECT
  CASE WHEN (SELECT COUNT(*) FROM Language WHERE PSID=3593031830770623 and Language='zg') > 0 THEN AdressZG
       WHEN (SELECT COUNT(*) FROM Language WHERE PSID=3593031830770623 and Language='uni') > 0 THEN Address
       ELSE Address
  END AS Address
FROM Classes WHERE ID = 1

Upvotes: 1

Related Questions