December 9, 2009 at 9:43 am
Hi,
I have two tables as below
CREATE TABLE [dbo].[IndirectEmp](
[EmpId] [int] NOT NULL,
[EmpName] [nvarchar](50) NULL,
[Salary] [int] NULL,
CONSTRAINT [PK_IndirectEmp] PRIMARY KEY CLUSTERED
(
[EmpId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[DirectEmp](
[EmpId] [int] NOT NULL,
[EmpName] [nvarchar](50) NULL,
[DeptId] [int] NULL,
[Salary] [int] NULL,
CONSTRAINT [PK_DirectEmp] PRIMARY KEY CLUSTERED
(
[EmpId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I have inserted values as
INSERT INTO [dbo].[DirectEmp] ([EmpId] ,[EmpName],[DeptId] ,[Salary])
VALUES (101 ,'aaa',10 ,1000)
INSERT INTO [dbo].[DirectEmp] ([EmpId] ,[EmpName],[DeptId] ,[Salary])
VALUES (102 ,'bbb',11 ,1100)
INSERT INTO [Employee].[dbo].[IndirectEmp]([EmpId] ,[EmpName],[Salary])
VALUES(201 ,'abc' ,2000)
INSERT INTO [Employee].[dbo].[IndirectEmp]([EmpId] ,[EmpName],[Salary])
VALUES(202 ,'dfs' ,3000)
So the DirectEmp consists of
101aaa101000
102bbb111100
and IndirectEmp consist of
201abc2000
202dfs3000
I want the result as below
EmpID Name DepID Salary
101aaa101000
102bbb111100
201abc -- 2000
202dfs-- 3000
One important fact I forgot to mention
EmpID of DirectEmp will not exist in the IndirectEmp table
I cannot perform union as the fields are not equal
I cannot perform join because there are no common fields
How do I group to get the desired result.
Thanks in advance
Regards
cmrhema
December 9, 2009 at 9:55 am
Here is one way:
select
[EmpId],
[EmpName],
cast([DeptId] as varchar) DeptId,
[Salary]
from
[dbo].[DirectEmp]
union all
select
[EmpId],
[EmpName],
'--',
[Salary]
from
[dbo].[IndirectEmp]
order by
[EmpId]
;
December 9, 2009 at 6:44 pm
Brilliant way Lynn.
It works
December 10, 2009 at 1:01 am
Another solution hope this too will work.
select
[EmpId] ,
[EmpName],
[DeptId],
[Salary]
from
[DirectEmp]
union
select
[EmpId],
[EmpName],
cast('--' as sql_variant) [DeptId],
[Salary]
from
[IndirectEmp]
order by
[EmpId]
Feel free to say if there is any mistake.
Thanks,
Chandru
December 10, 2009 at 6:30 pm
Chandru, yours also works.
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply