How do I join or merge these tables to get my desired result

  • 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

  • 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]

    ;

  • Brilliant way Lynn.

    It works

  • 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

  • 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