June 25, 2003 at 7:36 am
I have a requirement to create a file from a SQL query that will contain the output from a one to many relationship on a single record of the output file. Does anyone know of a way to code a join statement to output the result of a one to many relationship in a single row result set. The output record need only inlude 3 occurances of the multiple occuring row per record created. I know I can do this with a stored procedure using cursor processing, but was hoping for something simpler to avoid the extra coding.
June 25, 2003 at 7:47 am
Need a bit more info: when you say a single-row recordset, do you mean one row per record in the main (parent, 'one') table?
quote:
The output record need only inlude 3 occurances of the multiple occuring row per record created.
Do you mean there will be a maximum of three 'child' records per parent record?
If so, is theis because there are only three in the table, or do you need to select three records from those available? And if the latter, on what basis would you choose the records?
Table schema, fileds required, and example of what the resultset should look like would be helpful.
Depending on the answers to these questions, I suspect you may be able to do this by aliasing the child table three times (maybe using a temp table or two).
Tim
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 25, 2003 at 8:13 am
Do you mean there will be a maximum of three 'child' records per parent record?.....
Yes, a maximum of 3 child rows per parent.
I had tried aliasing the child rows but there is no specific where clause I can use to limit to a specific selection of the available children. Just retrieving 3 is the only requirement as there are rarely more than 3 and often none. My attempts at coding this so far have not given me the results I wanted. a simple example of what I want to do would be ...........
Table A_TABLE Table B_TABLE
A1_COL B1_COL
B2_COL
B3_COL
RESULT SET CONTAIN....
A1,B2,B3,B2,B3,B2,B3
Where columns A1 and B1 would be the primary and foreign keys.
Thanks!
June 26, 2003 at 8:21 am
Is there any data on the child records that identifies it's position within the parent? eg
TABLEA
IDA
1
2
3
TABLEA
IDA,IDB
1,1
1,2
2,1
2,2
2,3
3,1
Can u post structure of both tables, test data and expected result?
Edited by - davidburrows on 06/26/2003 08:21:30 AM
Far away is close at hand in the images of elsewhere.
Anon.
June 26, 2003 at 8:43 am
Additional. If your are producing a proc and don't mind using a temp table then this a possible solution.
create table TABLEA ([ID] int)
create table TABLEB ([ID] int,[Name] varchar(10))
insert into TABLEA values (1)
insert into TABLEA values (2)
insert into TABLEA values (3)
insert into TABLEB values (1,'Jack')
insert into TABLEB values (1,'Jill')
insert into TABLEB values (1,'John')
insert into TABLEB values (1,'Matthew')
insert into TABLEB values (2,'Luke')
insert into TABLEB values (2,'David')
insert into TABLEB values (3,'Ian')
CREATE TABLE #temp (RecID int IDENTITY(1,1),[ID] int,SeqNo int,[Name] varchar(10))
INSERT INTO #temp
SELECT A.ID,0,B.Name
FROM TABLEA A
INNER JOIN TABLEB B ON B.ID = A.ID
ORDER BY A.ID
UPDATE t
SET t.SeqNo = t.RecID - x.SubVal
FROM #temp t
INNER JOIN (SELECT [ID],MIN(RecID)-1 AS 'SubVal' FROM #temp GROUP BY [ID]) x ON x.ID = t.ID
SELECT A.ID,
MAX(CASE WHEN t.SeqNo=1 THEN t.Name ELSE '' END),
MAX(CASE WHEN t.SeqNo=2 THEN t.Name ELSE '' END),
MAX(CASE WHEN t.SeqNo=3 THEN t.Name ELSE '' END)
FROM TABLEA A
INNER JOIN #temp t ON t.ID = A.ID
GROUP BY A.ID
drop table TABLEA
drop table TABLEB
Far away is close at hand in the images of elsewhere.
Anon.
June 26, 2003 at 8:59 am
I'll try that.
Thanks!
June 26, 2003 at 9:49 am
Here's one without a temp table, but it does use multiple outer theta (inequality) joins...I'm sure SQL server will find some way of making the 4 joins to [child] a bit more efficient - at least it will probably sort the data from [main] and use merge joins rather than loops.
THe query brings back the first three child records ordering by [id] (primary key). If you have a wide primary key (e.g. varchar), the joins might be a bit sluggish.
[id] is primary key on both tables. [main_id] is the foreign key to [main] from [child].
I haven't included any other fields for simplicity, but you can would just add them to the 'select' list.
select m.id parentPK,
c1.id childPK1,
c2.id childPK2,
c3.id childPK3
from main m
left join child c1
on c1.main_id = m.id
left join child c2
on c2.main_id = m.id
and c1.id < c2.id
left join child c3
on c3.main_id = m.id
and c2.id < c3.id
left join child c_dummy
on
c_dummy.main_id = m.id
and
(
c_dummy.id < c1.id
or
(
c_dummy.id > c1.id
and c_dummy.id < c2.id
)
or
(
c_dummy.id > c2.id
and c_dummy.id < c3.id
)
)
where c_dummy.id is null
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply