April 18, 2006 at 6:11 pm
I am trying to create a view that returns data from three tables and can't seem to get it to return the data that I want. I am no SQL expert, so hopefully someone can give me some insight into what I need to do.
The tables are basically set up like this:
TABLE 1 |
PrimaryKey |
Textfield1 |
Textfield2 |
Textfield3 |
TABLE 2 |
PrimaryKey |
Table1ForeignKey |
Table3ForeignKey |
Textfield1 |
TABLE 3 |
PrimaryKey |
Textfield1 |
Textfield2 |
Textfield3 |
Table 1 and Table 3 are each joined to Table 2 on their respective Primary/Foreign Key fields and all of the Primary Keys are auto-incrementing.
I want the view to return all of the records from Table 1, even if there are no matching records in Table 2.
From Table 2 I only want data from the latest record(which should be the MAX(primarykey) ) for each record in Table 1.
I want the view to look something like this:
Table 1 PrimaryKey | Table1 Textfield1 | Table2 Textfield | Table3 Textfield |
In other words, I want to return one record in the view for each record in table 1, and I want the data from table 2 in each of those records to represent the last record added to table 2.
Can anyone enlighten me on the query necessary to get this view?
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply