> > SELECT mitarbeiter.ID, mitarbeiter.Name,
> > veranstaltung_hat_mitarbeiter.Veranstaltung_ID,
> > veranstaltung_hat_mitarbeiter.Assist
> > FROM mitarbeiter LEFT OUTER JOIN veranstaltung_hat_mitarbeiter ON
> > (mitarbeiter.ID = veranstaltung_hat_mitarbeiter.Mitarbeiter_ID AND
> > veranstaltung_hat_mitarbeiter.Veranstaltung_ID=1)
> >
> > The above query works fine with 'ODBC Test'. However with
> > Access 97 I only get the result of an inner join.
> > If I remove the second part of the on-join statement I get
> > an expected result (outer join with any 'veranstaltung_id').
> >
> > It seems to me Access 97 outer joins the tables using the first part
> > and performs a where-clause with the second statement afterwards.
> > This would lead exactly to the result I get.
> >
> > Any hint what might cause Access 97 to behave differently than
> > the OBDC test application?
> I would use:
> SELECT
> m.ID
> , m.Name
> , vhm.Veranstaltung_ID
> , vhm.Assist
> FROM
> mitarbeiter AS m
> LEFT JOIN veranstaltung_hat_mitarbeiter AS vhm
> ON (m.ID = vhm.Mitarbeiter_ID AND vhm.Veranstaltung_ID = 1)
> WHERE
> vhm.Mitarbeiter_ID IS NULL
I seems to me that the only difference between your and my
query is your where clause. If I would add it to my query the result
would be as follows:
a) with the ODBC test application I would get exaktly what I would like
to.
b) As I stated before, in Access 97 I get the result of an inner join.
So your WHERE clause would always lead to an empty result set.
Martin
