SQL max row of table regarding different table

Question

I have no clue of how to title this question, so I'm sorry if you got mislead by the title. Furthermore I'm sorry about german table-/column-names, feel free to ask if you don't understand anything.

I'll firstly explain the relevant part of my database:

I basically have to tables: Termine (appointments) and Buchungen (bookings). One Termin can have multiple Buchungen and one Buchung can have multiple Termine (So it's an n to n connection). They're connected with the table TermineBuchungen.

The table Termine has the columns Id and Datum (Date), the table Buchungen has Id and Bemerkung (Annotation) and the table TermineBuchungen (obviously) has Termin and Buchung.

To have all relevant information about a Buchung I need the Id and the Bemerkung of it as well as the Termin Id and Datum of the Termin regarding the Buchung which has the highest Datum - that means, the newest Termin regarding this Buchung.

What I want now is all Buchungen (that means all the relevant information about the Buchungen as explained above) which are connected to a specific Termin. This Termin however doesn't have to be the newest Termin of the Buchungen - that means, the returned (maximum) Termin of the Buchungen can be another one, than the one I initially searched for.

Let me explain this with the help of an example:

Termine:
Id    Datum
0     1/1/2000
1     1/1/2001
2     1/1/2002

Buchungen:
Id    Bemerkung
0     'a'
1     'b'
2     'c'

TermineBuchungne:
Termin    Buchung
0         0
0         1
1         1
2         1

Now I want all Buchung relevant informations for all Buchungen regarding Termin 0. The result should look like that:

Id    Bemerkung    NewestTermninId    NewestTerminDatum
0     'a'          0                  1/1/2000
1     'b'          1                  1/1/2001

Especially the second example is interesting, as the newest Termin here isn't the Termin 0.

I tried to achieve this like that:

SELECT
  b.Id,
  b.Bemerkung,
  t.Id AS TerminId,
  t.Datum AS TerminDatum
FROM Buchungen AS b
  INNER JOIN TermineBuchungen AS tb ON b.Id = tb.Buchung
  LEFT JOIN
  (
    SELECT t2.Id, t2.Datum
    FROM Termine AS t2
      INNER JOIN TermineBuchungen AS tb2 ON t2.Id = tb2.Termin AND tb2.Buchung = b.Id
    WHERE t2.Datum =
          (
            SELECT MAX(t3.Datum)
            FROM Termine AS t3
              INNER JOIN TermineBuchungen AS tb3 ON t3.Id = tb3.Termin AND tb3.Buchung = b.Id
          )
  ) AS t ON 1 = 1
WHERE tb.Termin = 1

But I'm getting the error The multi-part identifier "b.Id" could not be found.

I'm using a MS SQL Server.

I tried to find an answer to this question for a few hours now, but as mentioned, I don't quite know how to title this problem.

score 1 · Accepted Answer · answered Nov 27 '17 at 16:44

You should be able to solve your problem by using OUTER APPLY. OUTER and CROSS APPLY are used when you need to query correlated subqueries, exactly as in your case:

SELECT
  b.Id,
  b.Bemerkung,
  t.Id AS TerminId,
  t.Datum AS TerminDatum
FROM Buchungen AS b
  INNER JOIN TermineBuchungen AS tb ON b.Id = tb.Buchung
  OUTER APPLY
  (
    SELECT t2.Id, t2.Datum
    FROM Termine AS t2
      INNER JOIN TermineBuchungen AS tb2 ON t2.Id = tb2.Termin AND tb2.Buchung = b.Id
    WHERE t2.Datum =
          (
            SELECT MAX(t3.Datum)
            FROM Termine AS t3
              INNER JOIN TermineBuchungen AS tb3 ON t3.Id = tb3.Termin AND tb3.Buchung = b.Id
          )
  ) AS t ON 1 = 1
WHERE tb.Termin = 1

In the thread I attached to this answer, you can see that your case is 3) Reusing a column alias.

score 1 · Answer 2 · answered Nov 27 '17 at 17:08

Simpler query, including sample data:

-- sample data

;with Termine as (
    select * from (values 
        (0, '2000-1-1'),
        (1, '2001-1-1'),
        (2, '2002-1-1')
    ) t(Id, Datum)
),
Buchungen as (
    select * from (values 
        (0, 'a'),
        (1, 'b'),
        (2, 'c')
    ) t(Id, Bemerkung)
),
TermineBuchungen as (
    select * from (values 
        (0, 0),
        (0, 1),
        (1, 1),
        (2, 1)
    ) t(Termin, Buchung)
)

-- solution

select
    Id,
    Bemerkung,
    TerminId,
    TerminDatum
from (
    select b.Id, b.Bemerkung, TerminId = t.Id, TerminDatum = t.Datum, MaxTerminDatum = max(t.Datum) over(partition by b.Id) from Buchungen b
    join TermineBuchungen tb ON b.Id = tb.Buchung
    join Termine t on t.Id = tb.Termin
) t
where TerminDatum = MaxTerminDatum

It returns TerminId = 2 unlike in your example result because TerminId = 2 is indeed linked with Buchung = 1 and it has the highest date.

This seems to work fine as well, though I doubt that the query is simpler. — MetaColon, Nov 27 '17 at 17:12
It has less table references (3 against 4), will probably run faster and it has less characters. — Alexey, Nov 27 '17 at 17:16
I'd mark it as an answer as well, but as you can only do this for one answer and Rigertas answer is better explained and thus more understable for me, I'll leave his answer marked as answer. — MetaColon, Nov 27 '17 at 17:27

score -1 · Answer 3 · answered Nov 27 '17 at 17:02

Here is another solution:

SELECT
  b.Id,
  b.Bemerkung,
  t.Id AS TerminId,
  t.Datum AS TerminDatum
FROM Buchungen AS b
  INNER JOIN TermineBuchungne AS tb ON b.Id = tb.Buchung
  LEFT JOIN
  (
    SELECT t2.Id, t2.Datum, tb2.Buchung
    FROM Termine AS t2
      INNER JOIN TermineBuchungne AS tb2 ON t2.Id = tb2.Termin  
    WHERE t2.Datum =
          (
            SELECT MAX(t3.Datum) 
            FROM Termine AS t3
              INNER JOIN TermineBuchungne AS tb3 ON t3.Id = tb3.Termin 
              INNER JOIN Buchungen b ON tb3.Buchung = b.Id
          )
  ) AS t ON t.Buchung = b.Id
WHERE tb.Termin = 1

This doesn't seem to work, as the associated TerminId will be always null. — MetaColon, Nov 27 '17 at 17:05

SQL max row of table regarding different table

3 Answers3