CODEWITHSUNDEEP
sql-serverunion
blackstar012
blackstar012

Reputation: 37

SQL union for one identical column

I have a SQL table for the translation of my website like this :

TRANSLATE

I'm trying to give for users a custom translation, where the users can set one or more labels So he will have something like :

I want to Select all from TRANSLATE where Languague = "English"

Then Select all from TRANSLATE where Languague = "EnglishCustom"

And do a Union only for the Name's column. So if we have an identical Name (here: lbl_name), i give the priority on the EnglishCustom to get "N4M3", not "name".

I just want to understand how we can doing a : If we have 2 identical Label, so the user added a trad on this label, so GET the Translate on the EnglishCustom and not on the English, With a SQL request

Can you help me please ? thank you

Upvotes: 2

Views: 87

Answers (3)

Anagha
Anagha

Reputation: 918

Try This:

select id,Label,Translate,Languague
from
(select id,Label,Translate,Languague,ROW_NUMBER() over (partition by id,label order by languague desc) rn
from TRANSLATE
where Languague in('English','EnglishCustom')) a
where a.rn =1

OUTPUT:

id  Label       Translate   Languague
1   lbl_name    n4m3        EnglishCustom

Upvotes: 2

DKSan
DKSan

Reputation: 4197

If you got your query ready and just need to return the right values you might want to have a look into coalesce()

Lets assume this query (just to eyplain, might not work):

SELECT COALESCE(t2.TRANSLATE,t1.TRANSLATE)
FROM TRANSLATE t1
  LEFT JOIN TRANSLATE t2
         ON t1.label = t2.label
        AND t2.Languague = CONCAT (t1.Languague,'Custom')
WHERE t1.Languague = 'English'

COALESCE will return the first non null value. If the LEFT JOIN does not join to any custom name the default will be returned.

In this Pastebin i did a quick test with a MySQL table that should also work for you.

Upvotes: 0

Marian Nasry
Marian Nasry

Reputation: 819

SELECT TOP 1 * FROM 
(
SELECT * , 1 as Seq 
FROM TRANSLATE where Languague = "EnglishCustom"
UNION ALL 
SELECT * ,2 as Seq 
FROM TRANSLATE where Languague = "English"
) AS myTab
ORDER BY Seq

Upvotes: 0

Related Questions