List:MySQL ODBC« Previous MessageNext Message »
From:Martin Trzaskalik Date:May 14 1999 1:32pm
Subject:Re: Different result with Access 97 and ODBC Test
View as plain text  
> > 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

Thread
Different result with Access 97 and ODBC TestMartin Trzaskalik12 May
  • Re: Different result with Access 97 and ODBC TestChristian Mack12 May
    • Re: Different result with Access 97 and ODBC TestMartin Trzaskalik14 May