Return rows from one table that may or may not match rows from another table

[marrrrrrc]

Hi,

I need to match rows from one table to another table that may or may not have a match on one common column.... the problem begins with bringing back the most recent row in the other table.

So, the query I'm starting with is:

SELECT * FROM data LEFT OUTER JOIN other ON (data.id=other.data_id)

This query works great except that I need to the most recent row in the "other" table, not all the rows where "data.id = other.data_id" matches.

So from that I tried to do this:

SELECT * FROM data LEFT OUTER JOIN other ON (data.id=other.data_id) GROUP BY data.id

However that brings the first row with "data.id = other.data_id" and not the most recent.

Thanks

[jemajoign]

I'd be interested in the solution to this too. Right now, the only way I know how to do it with any decent performance is to use a cheap trick using string concatenation and de-concatenation.

I would post it here, but I'd be embarrassed if someone were to post a decent solution after that. So I'll post it if no one responds ;-).

But if you want a solution with bad performance, you can try something like this:

SELECT 
	data.id,
	(
		select top 1 OtherInfo
		from other
		where data_id=data.id
		order by datefield desc
	) OtherInfo
FROM data

Thomas

[jemajoign]

Whoops! Sorry, I forgot which forums I was on. That "top 1" is supposed to be LIMIT 1 at the end of the subquery. I used to see an edit post button, but I don't see it anymore.

[jemajoign]

Never mind, I think I came up with a pretty elegant solution! :cool:

(Good thing I didn't post my cheap trick.)

create table if not exists data(id int, title varchar(30));
create table if not exists other(data_id int, datefield datetime, otherinfo varchar(30));

insert into data(id, title) values
(1, 'title 1'), (2, 'title 2');

insert into other(data_id, datefield, otherinfo) values
(1, '2001-01-01', 'older row for 1'),
(1, '2002-01-01', 'newer row for 1'),
(2, '2001-01-01', 'only row for 2');


select distinct data.id, other.otherinfo, other.datefield
from data
left join other on data.id = other.data_id
left join (
	select data_id, max(datefield) latest_date
	from other
	group by data_id
) rep on data.id=rep.data_id and other.datefield=rep.latest_date
where not (other.data_id is not null and rep.data_id is null)

Let me know if you need me to explain.

Thanks,
Thomas

[tal17]

How about something like this instead; perhaps it is a little cleaner - you don't have to filter out so many null rows

SELECT DISTINCT data.id, other.otherinfo, other.datefield
FROM data
LEFT JOIN (
SELECT data_id, max(datefield) latest_date
FROM other
GROUP BY data_id
) rep on data.id=rep.data_id 
LEFT JOIN other on (data.id = other.data_id and other.datefield=rep.latest_date)