topic Re: join from table in two different database in Qlik Learning Discussions

join from table in two different database

Wed, 26 Nov 2014 13:44:59 GMT

I have got 2 table and I must to do a query, but these table are in two distinct data base.

table tab_prov (in database db_prov.accdb) table tab_paesi (in database db_paesi.accdb)

id_p provincie fk_p paesi

1 viterbo 1 corchiano

2 roma 1 vasanello

2 velletri

I must this query:

select

provincie,paesi

from tab_prov, tab_paesi where tab_prov. id_p = tab_paesi.fk_p

i have used two distinct odbc connection, but it is not exactly.

Help me.

Thank you

Re: join from table in two different database

alexandros17 — Wed, 26 Nov 2014 13:47:28 GMT

in qlik you can use

odbc connect to db1

load ... from tab1

odbc connect to db2

left join

load ... friom tab2

otherwise in the select you can specify the db names before tables

Re: join from table in two different database

its_anandrjs — Wed, 26 Nov 2014 13:54:29 GMT

Hi,

In qlikview load this tables with different ODBC connection string and make joins between them by common key fields see example for this

Table1:

tab_prov (in database db_prov.accdb)

id_p provincie

1 viterbo

2 roma

tab_paesi (in database db_paesi.accdb)

Table2:

fk_p

paesi

corchiano

vasanello

velletri

NewTable2:

Load

fk_p as id_p,

Paesi

Resident Table2;

Drop table Table2

Join

Load id_p, provincie

Resident Table1;

Drop Table Table1;

Let me know and check this way

Regards

Anand

Re: join from table in two different database

Wed, 26 Nov 2014 14:32:38 GMT

hello,

look this

ODBC CONNECT32 TO [odbc_prov;DBQ=C:\USERS\A.B\prova db

diversi\provincie.accdb];

ODBC CONNECT32 TO ;

this is ok

SELECT *

FROM provincie.accdb.cap_provincia , paesi.accdb.paesi_prov

This no

SELECT *

FROM provincie.accdb.cap_provincia , paesi.accdb.paesi_prov

ON provincie.accdb.cap_provincia.ID = paesi.accdb.paesi_prov.fk_prov;

Why it’s strange

Da: Alessandro Saccone

Inviato: mercoledì 26 novembre 2014 14:48

A: antonio bassanelli

Oggetto: Re: - join from table in two different

database

<http://community.qlik.com/> Qlik Community

join from table in two different database

reply from Alessandro Saccone

<http://community.qlik.com/people/Alexandros17?et=watches.email.thread> in

Education Services - View the full discussion

<http://community.qlik.com/message/666203?et=watches.email.thread#666203>

Re: join from table in two different database

its_anandrjs — Wed, 26 Nov 2014 14:37:51 GMT

Hi,

You have to provide two connection string as you say they are two different connection string.

ODBC CONNECT32 //For first database and then table load

Load ...

ODBC CONNECT32 //For Second database and then table load

See my suggest load script for this in below post..

Regards,

Anand

Re: join from table in two different database

Wed, 26 Nov 2014 14:41:38 GMT

hello,

look this

ODBC CONNECT32 TO [odbc_prov;DBQ=C:\USERS\A.B\prova db

diversi\provincie.accdb];

ODBC CONNECT32 TO ;

this is ok

SELECT *

FROM provincie.accdb.cap_provincia , paesi.accdb.paesi_prov

This no

SELECT *

FROM provincie.accdb.cap_provincia , paesi.accdb.paesi_prov

ON provincie.accdb.cap_provincia.ID = paesi.accdb.paesi_prov.fk_prov;

Why it’s strange

Da: Anand Chouhan

Inviato: mercoledì 26 novembre 2014 15:38

A: antonio bassanelli

Oggetto: Re: - join from table in two different

database

<http://community.qlik.com/> Qlik Community

join from table in two different database

reply from Anand Chouhan

<http://community.qlik.com/people/its.anandrjs?et=watches.email.thread> in

Education Services - View the full discussion

<http://community.qlik.com/message/666233?et=watches.email.thread#666233>

Re: join from table in two different database

its_anandrjs — Wed, 26 Nov 2014 14:52:01 GMT

Hi,

I believe it is not possible that directly check the values of two different fields in different database. Do either of this steps.

1. Load this tables and create the QVD and then do join in the tables as Alessandro suggest and also me in the post.

2. Directly on the SQL load but the database in the same connection string.

Or also you can try this steps

Step1

tab_prov (in database db_prov.accdb)

Table1: //First Connection String

id_p provincie

1 viterbo

2 roma

tab_paesi (in database db_paesi.accdb)

Table2://Second Connection String

fk_p

paesi

corchiano

vasanello

velletri

3. Load Resident table for renaming for same field name

NewTable2:

Load

fk_p as id_p,

Paesi

Resident Table2;

Drop table Table2

Right Join

Table1: //Load this table for the Right Join

Load id_p, provincie

Resident Table1;

Drop Table Table1;

Regards,

Anand

Re: join from table in two different database

Wed, 26 Nov 2014 15:02:38 GMT

Ok, after I will try

Thank You very much

See you later

Da: Anand Chouhan

Inviato: mercoledì 26 novembre 2014 15:52

A: antonio bassanelli

Oggetto: Re: - join from table in two different

database

<http://community.qlik.com/> Qlik Community

join from table in two different database

reply from Anand Chouhan

<http://community.qlik.com/people/its.anandrjs?et=watches.email.thread> in

Education Services - View the full discussion

<http://community.qlik.com/message/666284?et=watches.email.thread#666284>

Re: join from table in two different database

Thu, 27 Nov 2014 08:53:40 GMT

Hi,

you are right, I did as you said,

in the query, and only in the FROM , I specified the db names before tables

this is the query Exact :

SELECT cap_provincia.provincia,paesi_prov.paesi

FROM provincie.accdb.cap_provincia , paesi.accdb.paesi_prov

where cap_provincia.ID = paesi_prov.fk_prov;

Thank you so much.

Da: Alessandro Saccone

Inviato: mercoledì 26 novembre 2014 14:48

A: antonio bassanelli

Oggetto: Re: - join from table in two different

database

<http://community.qlik.com/> Qlik Community

join from table in two different database

reply from Alessandro Saccone

<http://community.qlik.com/people/Alexandros17?et=watches.email.thread> in

Education Services - View the full discussion

<http://community.qlik.com/message/666203?et=watches.email.thread#666203>