CODEWITHSUNDEEP
sqljoinouter-join
Steve Lopez
Steve Lopez

Reputation: 13

sql, outer join

I have two tables, linked with an outer join. The relationship between the primary and secondary table is a 1 to [0..n]. The secondary table includes a timestamp column indicating when the record was added. I only want to retrieve the most recent record of the secondary table for each row in the primary. I have to use a group by on the primary table due to other tables also part of the SELECT. There's no way to use a 'having' clause though since this secondary table is not part of the group.

How can I do this without doing multiple queries?

Upvotes: 0

Views: 227

Answers (4)

tdammers
tdammers

Reputation: 20706

Something like:

SELECT a.id, b.* 
FROM table1 a
INNER JOIN table2 b ON b.parentid = a.id
WHERE b.timestamp = (SELECT MAX(timestamp) FROM table2 c WHERE c.parentid = a.id)

Use LEFT JOIN instead of INNER JOIN if you want to show rows for IDs in table1 without any matches in table2.

Upvotes: 1

NGLN
NGLN

Reputation: 43664

The quickest way I know of is this:

SELECT
  A.*,
  B.SomeField
FROM
  Table1 A
  INNER JOIN (
    SELECT
      B1.A_ID,
      B1.SomeField
    FROM
      Table2 B1
      LEFT JOIN Table2 B2 ON (B1.A_ID=B2.A_ID) AND (B1.TimeStmp < B2.TimeStmp)
    WHERE
      B2.A_ID IS NULL
  ) B ON B.A_ID = A.ID

Upvotes: 0

gbn
gbn

Reputation: 432667

For performance, try to touch the table least times

Option 1, OUTER APPLY

SELECT *
FROM
   table1 a
  OUTER APPY
  (SELECT TOP 1 TimeStamp FROM table2 b
   WHERE a.somekey = b.somekey ORDER BY TimeStamp DESC) x

Option 2, Aggregate

SELECT *
FROM
  table1 a
  LEFT JOIN
  (SELECT MAX(TimeStamp) AS maxTs, somekey FROM table2
   GROUP BY somekey) x ON a.somekey = x.somekey

Note: each table is mentioned once, no correlated subqueries

Upvotes: 3

Steve Mayne
Steve Mayne

Reputation: 22858

select *
from table1 left outer join table2 a on
   table1.id = a.table1_id
where
  not exists (select 1 from table2 b where a.table1_id = b.table1_id and b.timestamp > a.timestamp)

Upvotes: 0

Related Questions