Martin Trzaskalik wrote:
>
> I am getting different results with Access 97 and the ODBC Test
> application with MyODBC 2.50.22...
>
> Let me add that I activated the 'Return matching rows' flag as
> recommended.
>
> I tried to use a work around for non supported subqueries.
> I am using a table 'mitarbeiter'. Its primary key is referenced
> by another table. However in the later table this foreign
> key is only part of the primary key.
>
> mysql> desc mitarbeiter;
> +-------------------+--------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +-------------------+--------------+------+-----+---------+----------------+
> | ID | int(5) | | PRI | 0 | auto_increment |
> | Name | varchar(100) | | | | |
> ...
>
> mysql> desc veranstaltung_hat_mitarbeiter;
> +------------------+--------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +------------------+--------+------+-----+---------+-------+
> | Veranstaltung_ID | int(3) | | PRI | 0 | |
> | Mitarbeiter_ID | int(3) | | PRI | 0 | |
> | Assist | int(1) | | | 0 | |
> +------------------+--------+------+-----+---------+-------+
>
> I would like to select all rows in 'mitarbeiter' where there
> is no corresponding entry in 'veranstaltung_hat_mitarbeiter'
> with 'Veranstaltung_ID=1'.
>
> I tried to do this by a outer join:
>
> 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?
>
> Thanks,
>
> Martin
Hi Martin
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
Tschau
Christian
