How To !! - Master Detail Query returning flat result set

Question

Post reply

How To !! - Master Detail Query returning flat result set

Simon Matthews

SSC-Addicted

Points: 452
More actions
February 4, 2006 at 5:19 am

#110960

Hi,
I need to query a master and detail table but returns records in on result set with each row containing the header plus a field for each detail. i.e.
MasterTable
Record1-Field1-Field2
DetailTable
Record1-Field1
Record2-Field1
Record3-Field1
The two tables are linked with an integer ID. I need to return for each master record the following:-
ResultRecord-MasterField1-MasterField2-Record1lField1-Record2lField1-Record3lField1
Any help would be appreciated as I'm quite new to this.
regards,
Simon.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Mathew J Kulangara SSCommitted Points: 1610 More actions · Answer 1

If I'm understanding you correctly..a simple join..

Select M.*,D.*

from MasterTable M

join DetailTable D

on M.ID = D.ID

HTH

Mathew J Kulangara
sqladventures.blogspot.com

Simon Matthews SSC-Addicted Points: 452 More actions · Answer 2

Thanks for the reply. I don't think I was very clear, I have updated the original post slightly.

Your example would return three records, I need to return one record which has the master table fileds and field1 from each of the master tables detail records.

thanks,

Simon.

Mathew J Kulangara SSCommitted Points: 1610 More actions · Answer 3

Still not sure exactly what you need. What is your DDL, and what is an example result you are expecting ?

Mathew J Kulangara
sqladventures.blogspot.com

[KH] SSCommitted Points: 1908 More actions · Answer 4

Is this what you want ?

create table #MasterTable

(

IDint,

Field1varchar(10),

Field2varchar(10)

)

create table #DetailTable

(

IDint,

Recordint,

Field1varchar(10)

)

insert into #MasterTable

select1, 'M - R1F1', 'M - R1F2' union all

select2, 'M - R2F1', 'M - R2F2' union all

select3, 'M - R3F1', 'M - R3F2'

insert into #DetailTable

select1, 1, 'D1 - R1F1'union all

select1, 2, 'D1 - R2F1'union all

select1, 3, 'D1 - R3F1'union all

select2, 1, 'D2 - R1F1'union all

select2, 2, 'D2 - R2F1'union all

select2, 3, 'D2 - R3F1'

select m.ID, m.Field1, m.Field2,

max(case when d.Record = 1 then d.Field1 else NULL end) as Detail_R1_F1,

max(case when d.Record = 2 then d.Field1 else NULL end) as Detail_R2_F1,

max(case when d.Record = 3 then d.Field1 else NULL end) as Detail_R3_F1

from#MasterTable m inner join #DetailTable d

onm.ID= d.ID

group by m.ID, m.Field1, m.Field2

drop table #MasterTable

drop table #DetailTable

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 5

Alternatively (using KH's table definitions):

select m.ID, m.Field1, m.Field2, d1.Field1 as Detail_R1_F1, d2.Field1 as Detail_R2_F1, d3.Field1 as Detail_R3_F1

from #MasterTable m

left join #DetailTable d1

on m.ID = d1.ID and d1.Record = 1

left join #DetailTable d2

on m.ID = d2.ID and d2.Record = 2

left join #DetailTable d3

on m.ID = d3.ID and d3.Record = 3

-- (add or leave out) where d1.Field1 is not null

Simon Matthews SSC-Addicted Points: 452 More actions · Answer 6

Thanks KH and Jesper. This was what I was looking for and has saved me a lot of head scratching !

If the Master and Detail Tables have a lot of entries, say 400,000 details across 50,000 master records and I use the above queries to search for data joining the detail table 6 times to the master table, will this have peformance issues ?

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 7

Make sure you have proper indexes on the tables involved. I suggest you equip the #Master table with a unique clustered index on ID and the #DetailTable with a unique clustered index on ID, Record. Then run a test to see which query is faster (I don't know the answer ). I will be disappointed if either query takes more than 10 minutes (and I guess it will be much faster).