SQL query - left outer join

[caff]

Hi,

I have a left outer join query that links two tables:

Table 1.
field_a char(10) <- primary key
field_b char(20)

Table 2.
field_c char(10) <- links to field_a in table 1
field_d datetime

There may be several records in table 2 for every record in table 1. field_c is not unique in table 2.

I want to join a left outer join from table 1 to table 2, but I only want to return the most recent value in table 2, i.e. the most recent datetime in field_d.

Any ideas on how to do this in a SQL query?

 

[Dj_Jestar]

group by field_c orderby field_d

 

[caff]

I'm not too familiar with the group by clause, but it's saying:

Column name 'field_d' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

Trouble is I don't want to group it by field_d - just but group_c as you suggested

Any ideas?
thanks

 

[Dj_Jestar]

are you selecting field_d?

 

[caff]

Yes

 

[nikebee]

select field_a,field_b,(select max (field_d) from table2 where field_c = x.field_a)
from @table1 x left outer join table2 on field_a = field_c
group by field_a,field_b


works a treat

if you have query analyser from the old days of SQL Server 2000 paste in the following

declare @table1 table (field_a char(10),field_b char(20))
declare @table2 table (field_c char(10),field_d datetime)

insert into @table1
select 'arse','hello'
insert into @table1
select 'bum','goodbye'
insert into @table2
select 'arse','01/01/1900'
insert into @table2
select 'arse','01/01/1910'
insert into @table2
select 'bum','01/01/2010'
insert into @table2
select 'bum','01/01/2000'


select field_a,field_b,(select max (field_d) from @table2 where field_c = x.field_a) from @table1 x left outer join @table2 on field_a = field_c
group by field_a,field_b

 

[Dj_Jestar]

the "a" word may be a naughty word, but nice to see others have similar test data to myself

 

[caff]

Cheers guys - that's worked a treat.

My SQL trigger is now stupidly complex

(on the bad side - tricky to debug later. on the good side - job security )

 

[nikebee]

the "a" word may be a naughty word, but nice to see others have similar test data to myself

HaHa, yeah I'm not the only one at work that does that either

If I'm testing something it's always been join/insert/group words like that

Others tend to use their names as variables or whatever... but if it's just testing and it's not going to make it into the main body of some code - who cares

 

[nikebee]

Cheers guys - that's worked a treat.

My SQL trigger is now stupidly complex

(on the bad side - tricky to debug later. on the good side - job security )

I'm sure it would be possible to do it without a nested select query, it might be worth playing around

 

[SiriusB]

The arse word is allowed.