April 18, 2006 at 8:07 pm
It would be easier if you would give a little more specific example on the names of the text fields, makes the code easier to follow.
But I think this is what Ur Looking for.
Create table TABLEA (PrimaryKey int identity, Textfield1 varchar(25),
Textfield2 varchar(25), Textfield3 varchar(25))
Create table TableB (PrimaryKey int identity,
Table1ForeignKey int,
Table3ForeignKey int,
Textfield1 varchar(25))
Create table TableC (PrimaryKey int Identity,
Textfield1 varchar(25),
Textfield2 varchar(25),
Textfield3 varchar(25))
Insert into TableA (TextField1, TextField2, TextField3)
Select 'Foo', 'FooTableAField2', 'FooTableATextField3' union
Select 'Bar', 'BarTableAField2', 'BarTableATextField3' union
Select 'Gan', 'GanTableAField2', 'GanTableATextField3'
Insert into TableC (TextField1, TextField2, TextField3)
Select 'Foo', 'FooTableCField2', 'FooTableCTextField3' union
Select 'Bar', 'BarTableCField2', 'BarTableCTextField3'
Insert TableB (Table1ForeignKey, Table3ForeignKey, Textfield1)
select 1, 1, 'TableA1B1' union
select 1, 2, 'TableA1B2' union
select 2, 1, 'TableA2B1'
Select tableA.Primarykey, TableA.Textfield1, DT.Textfield1, DT.TBTextfield1
from TableA
left join (select TableB.Table1ForeignKey, TableC.PrimaryKey, TableC.TextField1, TableC.TextField2, TableC.TextField3, TableB.Textfield1 as TBTextfield1
From TableC
Join TableB on TableB.Table3ForeignKey = TableC.PrimaryKey
where TableB.PrimaryKey in (select max(PrimaryKey)
From TableB
group by Table1ForeignKey)
)DT on DT.Table1ForeignKey = TableA.Primarykey
drop table TABLEA
drop table TABLEb
drop table TABLEc
April 20, 2006 at 7:24 am
That did the trick! Thanks very much for your help on this!
Cheers,
Keith
Viewing 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply