|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
DataRelation left outer join different database typesHello,
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, A left outer join doesn't return data from one table that doesn't exist in
another table. Show quote "Peter" <poostw***@home.nl> wrote in message news:1152650707.831390.130110@h48g2000cwc.googlegroups.com... > 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 wrote:
> A left outer join doesn't return data from one table that doesn't It doesn't return data, but it *will* return NULLs in the requested columns > exist in another table. 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 > "Peter" <poostw***@home.nl> wrote in message > news:1152650707.831390.130110@h48g2000cwc.googlegroups.com... >> 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). as described above? Or is there too much data involved? >> I need all the data from table 1 that doesn't exist in table 2, in Google returned this, but I can't vouch for it's use:>> 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. http://weblogs.sqlteam.com/davidm/archive/2004/01/20/748.aspx 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: Show quote > Brendan Green wrote: > > 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" <poostw***@home.nl> wrote in message > > news:1152650707.831390.130110@h48g2000cwc.googlegroups.com... > >> 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). > > 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? > > >> 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. > > Google returned this, but I can't vouch for it's use: > http://weblogs.sqlteam.com/davidm/archive/2004/01/20/748.aspx > > -- > Matt Noonan > EasyObjects.NET: The O/RM for the Enterprise Library > http://www.easyobjects.net 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. Show quote "Peter" <poostw***@home.nl> wrote in message news:1152650707.831390.130110@h48g2000cwc.googlegroups.com... > 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, >  |
|||||||||||||||||||||||
Other interesting topics