Query/View Question
-
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. 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 the latest record (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?
-
try this
select t1.PrimaryKey, t1.Textfield1, t2.Textfield1, t3.Textfield1
from TABLE1 t1 left join TABLE2 t2
on t1.PrimaryKey = t2.Table1ForeignKey
inner join TABLE3 t3
on t2.Table3ForeignKey = t3.PrimaryKey
-
The problem there is that it pulls ALL of the records from Table 2, not just the most recent for each corresponding record in Table 1.
Thanks for the try!
-
Darn system ate my post the first time through.
You don't mention any output from table3 so I'm leaving it out. I used some different column names, so you'll have to rearrange things to get them to work for you. A good way around that is to put the create and insert statements in your question.... next time!
Now, the First step is to return only the rows that are the max(PK) for each FK. To do this, I create a set that contains just that information, in an inline view called "X".
Then I join X back to Table2 on the maxPK = PK to get just the rows that correspond to the maxPK for each FK. This is the inline view called "Y".
This view is joined to Table1 in a leftwise fashion so that we get all the rows from Table1 and any matches from Table2.
hth jg
Create table #Table1 (
PK int,
txt1 varchar(39),
txt2 varchar(57)
)
Create table #Table2 (
PK int,
Table1FK int,
txt3 varchar(73)
)
Insert #Table1 (PK,txt1,txt2)
Select 1,'one','uno'
union
Select 2,'two','dos'
union
Select 3,'three','tres'
union
Select 4,'four','cuatro'
Insert #Table2 (PK,Table1FK,txt3)
Select 1,1,'not this'
union
Select 2,1,'yes this'
union
Select 3,2,'sure thing'
union
Select 4,3,'Not this, either'
union
Select 5,3,'Indeed'
Select t1.*,Y.txt3
from #Table1 t1
Left outer join
(Select X.Table1FK,t2.txt3 from #Table2 t2
Inner join (Select Table1FK,max(PK) as maxPK from #Table2 group by Table1FK) as X
on X.maxPK = t2.PK) as Y
On Y.Table1FK = t1.PK
drop table #Table1
drop table #Table2
-
Works perfectly, and with your explanation now it even makes sense to me
Thanks for you help on this!
Cheers,
Keith
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply