what i want is to union sal and sal_inc so that i can get the details of salary from sal and sal_inc.
After getting the salary details i want to join with emp_details
following is my query
select * from emp_details where empid in (select empid from sal union select empid from sal_inc)
but if i do the above i cant see the sal and sal_inc from sal and sal_inc table
with CTE as
(
select empid, sal from sal
union all
select empid, sal_inc from sal_inc
)
select *
from emp_details as e
join CTE as c on c.empid = e.empid
select emp_details.empid,empname, sal from emp_details,sal
where emp_details.empid=sal.empid
union all
select emp_details.empid,empname, sal_inc from emp_details,sal_inc
where emp_details.empid=sal_inc.empid
to get any specific order you can use
select*from
(select emp_details.empid,empname, sal from emp_details,sal
where emp_details.empid=sal.empid
union all
select emp_details.empid,empname, sal_inc from emp_details,sal_inc
where emp_details.empid=sal_inc.empid) empInfo order by empid
Shuvo
If more posts give you useful answers, Please mark each post as "Answer".
If you want to display sal and sal_inc in one column, you could use STUFF to combine them. Please check below query.
with CTE as
(
select empid, sal from sal
union all
select empid, sal_inc from sal_inc
)
select distinct e.empid,empname
,sals= stuff((select ','+convert(varchar,sal) from CTE t where empid=e.empid for xml path('')), 1, 1, '')
from emp_details as e
left join CTE as c on c.empid = e.empid
Thanks.
Please mark the replies as answers if they help or unmark if not.
Feedback to us
nicklibee
Member
650 Points
692 Posts
Table joins
Jun 16, 2012 05:20 AM|LINK
Dear all,
I have following tables
emp_details
empid empname
1 a
2 b
sal
empid sal
1 100
2 200
sal_inc
empid sal_inc
1 200
4 100
what i want is to union sal and sal_inc so that i can get the details of salary from sal and sal_inc.
After getting the salary details i want to join with emp_details
following is my query
select * from emp_details where empid in (select empid from sal union select empid from sal_inc)
but if i do the above i cant see the sal and sal_inc from sal and sal_inc table
plse help me
thanks
nick
ahamedthambi
Member
131 Points
118 Posts
Re: Table joins
Jun 16, 2012 05:35 AM|LINK
select emp_details.empname sal.sal, sal_inc.sal_inc from sal join on sal.empid=sal_inc.empid
join
emp_details
on emp_details.empid= sal_inc.empid
RameshRajend...
Star
7983 Points
2099 Posts
Re: Table joins
Jun 16, 2012 05:56 AM|LINK
Hai
Select * from emp_details inner join sal_inc on emp_details.empid = sal_inc.empid inner join sal on sal.empid =sal_inc.empid
Thank u
nicklibee
Member
650 Points
692 Posts
Re: Table joins
Jun 16, 2012 05:59 AM|LINK
Ok thanks your code works but I want the sal and sal_in to be one field, if i go by your way there will be two coluimns.
I want one column so thats the reason why i am using union if i use union i can get the details as one column (sal and sal_in)
ad want to join to emp_details
pls suggest me a way out
spapim
Contributor
2666 Points
393 Posts
Re: Table joins
Jun 16, 2012 05:21 PM|LINK
Try:
with CTE as ( select empid, sal from sal union all select empid, sal_inc from sal_inc ) select * from emp_details as e join CTE as c on c.empid = e.empidHope this helps.
www.imobiliariasemsuzano.com.br
Shuvo Aymon
Contributor
4820 Points
1167 Posts
Re: Table joins
Jun 16, 2012 06:13 PM|LINK
spapim is suggesting the right answer
you can also use
select emp_details.empid,empname, sal from emp_details,sal
where emp_details.empid=sal.empid
union all
select emp_details.empid,empname, sal_inc from emp_details,sal_inc
where emp_details.empid=sal_inc.empid
to get any specific order you can use
select*from
(select emp_details.empid,empname, sal from emp_details,sal
where emp_details.empid=sal.empid
union all
select emp_details.empid,empname, sal_inc from emp_details,sal_inc
where emp_details.empid=sal_inc.empid) empInfo order by empid
If more posts give you useful answers, Please mark each post as "Answer".
Chen Yu - MS...
All-Star
21829 Points
2513 Posts
Microsoft
Re: Table joins
Jun 20, 2012 09:47 AM|LINK
Hi nicklibee,
If you want to display sal and sal_inc in one column, you could use STUFF to combine them. Please check below query.
with CTE as ( select empid, sal from sal union all select empid, sal_inc from sal_inc ) select distinct e.empid,empname ,sals= stuff((select ','+convert(varchar,sal) from CTE t where empid=e.empid for xml path('')), 1, 1, '') from emp_details as e left join CTE as c on c.empid = e.empidThanks.
Feedback to us
Develop and promote your apps in Windows Store