Connecting to two different data sources and limiting data in tables.

[#1]

Hi,

I have two database sources.

connect(Datasource1)

Load A,B from table_1;

connect(Datasource2)

Load A,C from table_2;

Now, table_2(from Datasource2) contains redundant data and hence I would like to limit it to the data that is present only in table_1(from Datasource1)

e.g

In table_1

value of A = {1,2,3}

In table_2

value of A = {1,2,3,4,5}

but In table_2 I would like to load values which corresponds to A={1,2,3}

Can anyone help me with this?

Please let me know if you need further information.

Regards,

Rohit

[jonathandienst]

Hi

You can use a LEFT JOIN to get that:

connect(Datasource1);
DataTable:
Load A,B from table_1;
connect(Datasource2)
Left Join (DataTable)
Load A,C from table_2;

Jonathan

[#3]

Thanks!!

That solves my problem 🙂

[#4]

Hi,

Although now it shows the correct data but still the complete data is beeing loaded from table_2.

table_2 contains lot of redundant data and also takes a lot of time to load all the data.

My ultimate aim is to limit the data that is beeing loaded.

Is that possible?

[Miguel_Angel_Baeyens]

Hello,

Further to Jonathan's example, use the EXISTS() function in WHERE statement

ODBC CONNECT TO Database1;Table1:LOAD A, B;SQL SELECT A, B, FROM Database2.Table1; ODBC CONNECT TO Database2;Table2:LOAD A, CWHERE EXISTS(A);SQL SELECT A, CFROM Database2.Table2;

Hope that helps

[jonathandienst]

Rohita

Both my Join and Miguel's Exists work in QV, so all the data must first be fetched from the DB.

You can limit the data being fetched by doing the join in the SQL query. This will only fetch the matching data (and does it in a single fetch):

connect(Datasource1);
Load T1.A, T1.B, T2.C from table_1 As T1
LEFT JOIN table_2 As T2 ON T1.A = T2.A;

(The syntax will depend on the type of DB you are using. This should work on most)

Jonathan