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:

```sql
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.