List:MySQL ODBC« Previous MessageNext Message »
From:Christian Mack Date:May 12 1999 5:02pm
Subject:Re: Different result with Access 97 and ODBC Test
View as plain text  
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

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