How to write SQL Server query which can give result from any of 4 tables -
i have 4 tables (say emp1, emp2, emp3, emp4) identical columns. want fetch details
(select empid, empname emp1 empid = '1' ) union (select empid, empname emp2 empid = '1') union (select empid, empname emp3 empid = '1') union (select empid, empname emp4 empid = '1') the thing if got result first query (emp1) should ignore queries below (emp2, emp3, emp4). if result emp2, should ignore (emp3, emp4) , on.
remember in emp1, emp2, emp3, emp4 there different empname associated same empid. that's why union giving results. in case have prefer result uppermost table i.e emp1 > emp2 > emp3. tried using 'case' things not working me.
sample data
emp1
1 deepak emp2
1 nitin emp3
1 sateesh emp4
1 chandra and expected result is
1 deepak i hope clear you. please me thank you
you can add arbitrary column specify priority.
;with cte as( select *, n = 1 emp1 empid = 1 union select *, n = 2 emp2 empid = 1 union select *, n = 3 emp3 empid = 1 union select *, n = 4 emp4 empid = 1 ) ,ctern as( select *, rn = row_number() over(order n) cte ) select empid, empname ctern rn = 1 basically, want prioritize results emp1, emp2 , on. arbitrary column n comes in. want rank them in order of priority. result of first cte is:
empid empname n ----------- ---------- ----------- 1 deepak 1 1 nitin 2 1 sateesh 3 1 chandra 4 then use row_number add sequential number each rows. second cte, ctern give you:
empid empname n rn ----------- ---------- ----------- -------- 1 deepak 1 1 1 nitin 2 2 1 sateesh 3 3 1 chandra 4 4 laslt, want row least rn, add where rn = 1 clause. final result be:
empid empname ----------- ---------- 1 deepak additionally, can add partition empid on rn = row_number() over(order n)