CODEWITHSUNDEEP
sqlmultiple-columns
tok74
tok74

Reputation: 19

Query to show one column multiplied

I have the following 3 tables;

Table_Names:

user_id   Name  
------------------
  1       Mark
  2       Tom
  3       Ana

Table_Language:

language_id    Language
-----------------------
    1          English
    2          German

Table_Name_Lang

id   user_id   language_id
---------------------------
 1     1          1
 2     1          2
 3     2          1
 4     3          2

How can I create a query to show the expected results like those below?

Name    Expr_1_Eng   Expr_1_Ger
---------------------------------
Mark    English      German
Tom     English
Ana                  German

Thanks Tok

Upvotes: 1

Views: 49

Answers (3)

Ali Eshghi
Ali Eshghi

Reputation: 1243

I think you should use pivot: 

   select n.name,[0] as language_one,[1] as language_two,[2],[3] from 
    (select n.name,tl.language
    tablename n
    left join Table_Name_Lang tnl on n.userid=tnl.userid
    left join table_language tl on tl.id=tnl.id
    group by n.name,tl.language
    )T
    pivot
    (
    language
    for name in [0],[1],[2],[3]
    )AS PivotTable;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I would do:

select n.name,
       max(case when l.language = 'English' then l.language end) as has_English,
       max(case when l.language = 'German' then l.language end) as has_German
from names n join
     name_lang nl
     on nl.user_id = n.user_id join
     lang l
     on nl.language_id = l.language_id
group by n.name

Upvotes: 1

D-Shih
D-Shih

Reputation: 46219

You can try to use JOIN with condition aggregate function 

SELECT Name,
    MAX(CASE WHEN tnl.language_id = 1 then tl.Language end),
    MAX(CASE WHEN tnl.language_id = 2 then tl.Language end)
FROM 
Table_Name_Lang tnl 
JOIN Table_Names tn on tnl.language_id = tn.language_id
JOIN Table_Language tl on tl.user_id = tnl.user_id
GROUP BY Name

Upvotes: 0

Related Questions