MSSQL – SELECT COALESCE

Problem: WHERE X = ako je dostupno A selektiraj A,  a ako nije selektiraj B ako je B dostupno, a ako nije ni B dostupno selektiraj C…


SELECT * FROM tbl_test WHERE  tbl_test.lang = (
COALESCE(
(SELECT TOP 1 lang FROM tbl_test2 WHERE  lang='mk' ),
(SELECT TOP 1 lang FROM tbl_test2 WHERE lang='en'),
'hr'
)

No što ako želimo selektirati ovako: ako je dostupno A selektiraj A,  a ako nije selektiraj B ako je B dostupno, a ako nije ni B dostupno selektiraj C ali ne u WHERE uvjetu nego u samom selectu:

SELECT
COALESCE(TBL1.partner_id,TBL2.partner_id) as partner_id,
COALESCE(TBL1.event_title,TBL2.event_title) as event_name,
COALESCE(TBL1.lang,TBL2.lang) as lang
FROM
(SELECT partner_id, lang, event_title FROM tbl_a WHERE tbl_a.lang = 'HR') AS TBL1
FULL OUTER JOIN
(SELECT tbl_a.partner_id,tbl_a.lang,tbl_a.event_title FROM tbl_a WHERE tbl_a.lang = 'EN') AS TBL2
ON TBL1.partner_id = TBL2.partner_id
This entry was posted on Thursday, May 24th, 2018 at 1:50 pmand is filed under MSSQL Database Development. You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.

Comments are closed.