Master Detail SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Master Detail SQL Query

Author

Topic

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2011-10-13 : 04:26:33

hi,

I've been creating a query for almost two days but i'm not able to create my desired output.
here is the sample data

declare @EmpTable Table (empID int,empName varchar(25))
declare @Paycheck Table (checkID varchar(15),empId int)
declare @Deduction Table (deductionID varchar(15),checkid varchar(15))
declare @Earning Table (earningID varchar(15),checkid varchar(15))

insert into @empTable(empID,empName)
select 1,'emp_one'

insert into @Paycheck(checkID,empID)
select 'check100',1

insert into @Deduction(deductionID,checkid)
select 'deduction100','check100'
union all
select 'deduction101','check100'
union all
select 'deduction102','check100'

insert into @Earning(earningID,checkid)
select 'earning222','check100'
union all
select 'earning223','check100'
union all
select 'earning224','check100'
union all
select 'earning225','check100'

I want the output of the query to be like this

1 check100 deduction100 earning222
1 check100 deduction101 earning223
1 check100 deduction102 earning224
1 check100 NULL earning225

is it possible?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-13 : 04:47:26

what is the relationship between Deduction & Earning ? how do you match it ?

KH
[spoiler]Time is always against us[/spoiler]

stepson
Aged Yak Warrior

545 Posts

Posted - 2011-10-13 : 04:57:08

you must have a relationship between Deduction and Earning

a solutin if is not a relationship is to use row_number ....

is not the exact output but close

sselect * from @empTable E
inner join @Paycheck P on E.empid=P.empID

full join (
select ROW_NUMBER() OVER(PARTITION BY earning.checkID ORDER BY earningID asc) as i
, earningID,earning.checkID ,empID
from @earning
inner join @Paycheck on earning.checkID=Paycheck.checkID
)EA on p.checkID=EA.checkID

full join (
select ROW_NUMBER() OVER(PARTITION BY deduction.checkID ORDER BY deductionID asc) as i
, deductionID,deduction.checkID,empID
from @deduction
inner join @Paycheck on deduction.checkID=Paycheck.checkID
)D on P.checkID=D.checkID and D.i=EA.i

S

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2011-10-13 : 05:49:58

khtan
there's no relationship between those two tables except for the "checkID".

stepson
i'll try your solution if it works..

thank you guys for the reply..so far i am thinking if CTE will work.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-13 : 06:27:30

OK. This is using CTE


; with deduction as
(
	select	checkID, deductionID, 
		row_no = row_number() over (partition by checkID order by d.deductionID)
	from	@Deduction d
),
earning as
(
	select	checkID, earningID,
		row_no = row_number() over (partition by checkID order by e.earningID)
	from	@Earning e
),
match as
(
	select	checkID	= coalesce(d.checkID, e.checkID),
		d.deductionID, e.earningID
	from	deduction d
		full outer join earning e	on	d.checkID	= e.checkID
						and	d.row_no	= e.row_no
)
select	p.empID, p.checkID, m.deductionID, m.earningID
from	@Paycheck p
	inner join match m	on	p.checkID	= m.checkID

KH
[spoiler]Time is always against us[/spoiler]

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2011-10-13 : 21:47:38

khtan

your CTE worked. :) you gave me idea on how to use CTE in other way..

thank you so much..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-13 : 23:09:16

you are welcome

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources