CODEWITHSUNDEEP
sql-servert-sql
Ching
Ching

Reputation: 5

Multiple self joins

I'm working on a table named ERRMSG. Errors are in 4 languages (there is a column named LANGU that has values L1, L2, L3, L4). MSGKEY is the app name and MSGNUM is its ID. MSGKEY and MSGNUM together is unique if you select a single language. I'll get them translated so I am creating a translation table in which each translation will be in a single row. My query is getting duplicates :

SELECT  X.MSGKEY, X.MSGNUM,
    ISNULL((L1.MESSAGE),'') AS L1TEXT,
    ISNULL((L2.MESSAGE),'') AS L2TEXT,
    ISNULL((L3.MESSAGE),'') AS L3TEXT,
    ISNULL((L4.MESSAGE),'') AS L4TEXT
FROM ERRMSG X
FULL JOIN ERRMSG L1 ON (X.MSGKEY = L1.MSGKEY AND X.MSGNUM = L1.MSGNUM AND L1.LANGU = 'L1')
FULL JOIN ERRMSG L2 ON (X.MSGKEY = L2.MSGKEY AND X.MSGNUM = L2.MSGNUM AND L2.LANGU = 'L2')
FULL JOIN ERRMSG L3 ON (X.MSGKEY = L3.MSGKEY AND X.MSGNUM = L3.MSGNUM AND L3.LANGU = 'L3')
FULL JOIN ERRMSG L4 ON (X.MSGKEY = L4.MSGKEY AND X.MSGNUM = L4.MSGNUM AND L4.LANGU = 'L4')

What am I doing wrong ? I am guessing the join type might solve it but can't figure out how.

Upvotes: 0

Views: 50

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

I think this can be solved using conditional aggregation:

SELECT  MSGKEY, 
        MSGNUM,
        MAX(CASE WHEN LANGU = 'L1' THEN MESSAGE END) AS L1TEXT,
        MAX(CASE WHEN LANGU = 'L2' THEN MESSAGE END) AS L2TEXT,
        MAX(CASE WHEN LANGU = 'L3' THEN MESSAGE END) AS L3TEXT,
        MAX(CASE WHEN LANGU = 'L4' THEN MESSAGE END) AS L4TEXT
FROM ERRMSG 
GROUP BY MSGKEY, MSGNUM

Upvotes: 1

Related Questions