Share
Go down
avatar
Admin
Posts : 155
Join date : 2017-11-11
Age : 27
View user profilehttp://net-media.yoo7.com

How to get one to many relationship data?

on Wed Dec 13, 2017 2:59 pm
These are two tables having one-to-many relationship:
Code:
Employee[Table]:
---------------------------------------------------
EmpId  | Name  | Country | Salary  | Email
--------------------------------------------------
1        John    USA      875847  john@test.com
2        Mike    USA      785487  mike@test.com

Lincense[Table]
----------------------------------------
EmpId  | LicenseType  | LincenseNumber
----------------------------------------
1        LincenseType1  12345678
1        LincenseType2  87654321
1        LincenseType3  78945613
2        LincenseType1  12345678
2        LincenseType2  87654321
2        LincenseType3  78945613


EmployeeDetails[Expected ResulSet]
-----------------------------------------------------------------------------------------------
EmpId  | Name  | Country | LicenseType    | LicenseNumber | Salary    | Email
-----------------------------------------------------------------------------------------------
1        John    USA      LincenseType1        12345678    875847    john@test.com
                            LincenseType2        87654321
                            LincenseType3        78945613
2        Mike    USA      LincenseType1        12345678    785487    mike@test.com
                            LincenseType2        87654321
                            LincenseType3        78945613
To get result in above expected format what would be the best way to achieve that so that result contain only one row for Employee detail and all associated License details?

Answers
This here will do the trick for you. Remember if your empid and salary are int columns you can only null them or set 0 into it. Otherwise it need to be of type string

SQL Code
Code:
declare @emp table (empid int,[name] nvarchar(50),Country nvarchar(50),Salary int,[Email] nvarchar(50)
)
insert into @emp
values
(1        ,'John',  'USA',      875847,  'john@test.com'),
(2        ,'Mike',    'USA',      785487,  'mike@test.com')

declare @lic table (empid int, licensetype nvarchar(50),licencenumber int)
insert into @lic
values



(1        ,'LincenseType1',  12345678),
(1        ,'LincenseType2',  87654321),
(1        ,'LincenseType3',  78945613),
(2        ,'LincenseType1',  12345678),
(2        ,'LincenseType2',  87654321),
(2        ,'LincenseType3',  78945613)

select
empid  = case when rn > 1 then null else x.empid end,
[name] = case when rn > 1 then '' else [name] end,
Country = case when rn > 1 then '' else country end,
licensetype = licensetype,
licencenumber = licencenumber,
Salary = case when rn > 1 then '' else Salary end,
Email = case when rn> 1 then '' else Email end

 from (
select a.empid,[name],country,licensetype,licencenumber,Salary,Email,ROW_NUMBER() over(partition by a.empid order by licensetype) as rn from @emp  a left join @lic b on a.empid = b.empid
)x
SQL Update If Lincencetype should always be 1 on first row you can just do it like this. This will be faster
Code:
select
empid  = case when licensetype !='LincenseType1' then null else a.empid end,
[name] = case when licensetype !='LincenseType1'  then '' else [name] end,
Country = case when licensetype !='LincenseType1' then '' else country end,
licensetype = licensetype,
licencenumber = licencenumber,
Salary = case when licensetype !='LincenseType1'  then '' else Salary end,
Email = case when licensetype !='LincenseType1'  then '' else Email end


 from @emp  a inner join @lic b on a.empid = b.empid


Back to top
Permissions in this forum:
You can reply to topics in this forum