CODEWITHSUNDEEP
sqlsql-server
Pallav Jha
Pallav Jha

Reputation: 43

Sql alias not giving expected result with join

This is my sql query:

Select  'P'+(cast(p.Id as varchar)) as  SolrId,
(Select lp.LocaleValue where LanguageId=3 and lp.EntityId=p.Id)  as fr_Name ,
(Select lp.LocaleValue where LanguageId=2) as hi_Name,
(Select lp.LocaleValue where LanguageId=4) as nl_Name,* from Product p
LEft  Join LocalizedProperty lp on EntityId=p.Id

And i am getting this result actual result but i want to fetch the data like this expected result Can anyone help me?

Upvotes: 1

Views: 55

Answers (3)

Pallav Jha
Pallav Jha

Reputation: 43

This is working fine

Select 'P' + (cast(p.Id as varchar(255))) as SolrId,(cast(p.Name as varchar(MAX))) as Name,
           max(case when lp.LanguageId = 3 then lp.LocaleValue end) as fr_name,
           max(case when lp.LanguageId = 2 then lp.LocaleValue end) as hi_name,
           max(case when lp.LanguageId = 4 then lp.LocaleValue end) as nl_name,
           max(case when lp.LanguageId = 4 then lp.LocaleValue end)  
    from Product p left join
         LocalizedProperty lp
         on lp.EntityId = p.Id 
    group by p.id,p.Name

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I would just use conditional aggregation:

Select 'P' + (cast(p.Id as varchar(255))) as SolrId,
       max(case when lp.LanguageId = 3 then lp.LocaleValue end) as fr_name,
       max(case when lp.LanguageId = 2 then lp.LocaleValue end) as hi_name,
       max(case when lp.LanguageId = 4 then lp.LocaleValue end) as nl_name
from Product p left join
     LocalizedProperty lp
     on lp.EntityId = p.Id 
group by p.id;

Upvotes: 1

Thomas Koelle
Thomas Koelle

Reputation: 3742

join lp with itself and give it 4 different aliases

select lp1.LocalValue, lp2.Localvalue from lp lp1
    join lp lp2 on lp1.pId = lp2.pId
where
    lp1.Languageid = 3 and lp2.Languageid = 2

Upvotes: 0

Related Questions