DataRelation left outer join different database types

[Peter]

Hello,

I'm trying to create an application that reads data from two tables,
where the one table is stored in a MySQL database (Table 1) and the
other is stored in SQL Server (Table 2).
I need all the data from table 1 that doesn't exist in table 2, in
other words I want to left outer join table 1 on table 2. I prefer not
to use the linked server functionality or import the MySQL data into
SQL Server.
Is it possible to solve this using ADO.NET, for instance using the
DataRelation? It seems like the DataRelation only supports inner joins,
however maybe I'm missing parts of the functionality.

Best regards,

 

[Brendan Green]

A left outer join doesn't return data from one table that doesn't exist in
another table.

 

[Matt Noonan]

A left outer join doesn't return data from one table that doesn't
exist in another table.

It doesn't return data, but it *will* return NULLs in the requested columns
from the 2nd table (assuming the join is legal). So you could LEFT JOIN the
2nd table and then add a WHERE [tableBcolumn] IS NULL.

Peter, can you push the MySql data into SQL Server and then do the LEFT JOIN
as described above? Or is there too much data involved?

Google returned this, but I can't vouch for it's use:
http://weblogs.sqlteam.com/davidm/archive/2004/01/20/748.aspx

 

[Peter]

Hello Matt,

Unfortunately pushing the data is not an option. I also found the post
you found, however I feel like it's not really a fast and stable
option. However, joining with two different databases will never be
really fast I guess...
I will try to create a situation where a shadow table will be stored in
the MySQL database. This way I can limit the number of records that
need to be compared with the MSSQL database.
I will post the results.

Thanks for your answer!

Regards,

Peter

Matt Noonan schreef:

A left outer join doesn't return data from one table that doesn't
exist in another table.

It doesn't return data, but it *will* return NULLs in the requested columns
from the 2nd table (assuming the join is legal). So you could LEFT JOIN the
2nd table and then add a WHERE [tableBcolumn] IS NULL.

Peter, can you push the MySql data into SQL Server and then do the LEFT JOIN
as described above? Or is there too much data involved?

Google returned this, but I can't vouch for it's use:
http://weblogs.sqlteam.com/davidm/archive/2004/01/20/748.aspx

 

[Adrian Moore]

Peter,

You might be interested in the assembly I've been working on at
http://www.queryadataset.com. It lets you perform complex SQL SELECT
statements including UNION, JOINS, GROUP BY, HAVING, ORDER BY, sub-queries,
functions, etc against the DataTables in a dataset.

Thanks
Ad.