Query/View Question

Question

Post reply

Query/View Question

Viewing 4 posts - 1 through 5 (of 5 total)

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

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

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

Keith Malwitz SSC Enthusiast Points: 113 More actions · Answer 2

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!

Jeff Gray SSChampion Points: 10667 More actions · Answer 3

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

Keith Malwitz SSC Enthusiast Points: 113 More actions · Answer 4

Works perfectly, and with your explanation now it even makes sense to me

Thanks for you help on this!

Cheers,

Keith