Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

Not applicable

join from table in two different database

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

es

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

Tags (1)
8 Replies

Re: join from table in two different database

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

Hi,

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

1.

Table1:

tab_prov  (in database db_prov.accdb)                                 

     id_p     provincie                                                                      

       1           viterbo                                                                       

       2           roma                                                                          

 

2.                                                                                                         

tab_paesi (in database db_paesi.accdb)

Table2:

fk_ppaesi
corchiano
vasanello
velletri

3.

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

Not applicable

Re: join from table in two different database

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

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

Not applicable

Re: join from table in two different database

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

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                                                                          

 

2.                                                                                                         

tab_paesi (in database db_paesi.accdb)

Table2://Second Connection String

fk_ppaesi
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

Not applicable

Re: join from table in two different database

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>

Not applicable

Re: join from table in two different database

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>

Community Browser