Joining on multiple tables in teradata

Database
Enthusiast

Joining on multiple tables in teradata

Hi All,

Please help me through this

sel

a.col1,

a.co2,

a.col3,.........b.col1,b.col2..,c.col1,c.col2

from table1 as a inner join table2 as b on a.col1 =b.col1

inner join table 3 as c on a.col1 = b.col1

where col1 = xxxxx;

Now i need join one more table table4. As table4 dont have col1 as primary index in it I need to join this to another table which has Primary key.

The below is the different query which i need inculde this in to the above sel statement.

Sel

xx.col1,

yy.aaa,yy.bbb,zz.ccc,zz.ddd,zz.eee

from tablea as xx, tableb as yy, table4 as zz

where xx.col1 = yy.bbb and yy.aaa = zz.ccc

Primary indexs : col1 for table1,table2,table3,tablexx

                        : aaa for tableb

                        :ccc for table4

Thanks in advance

5 REPLIES
Enthusiast

Re: Joining on multiple tables in teradata

My trail:

sel

a.col1,

a.co2,

a.col3,.........b.col1,b.col2..,c.col1,c.col2,zz.ccc,zz.ddd,zz.eee

from table4 as zz,table1 as a inner join table2 as b on a.col1 =b.col1

inner join table 3 as c on a.col1 = b.col1

inner join tableb as yy on a.col1= yy.bbb and yy.aaa = zz.ccc

where col1 = xxxxx;

Error: 3782 Improper coloumn reference in the search condtion of the joined table

Thanks

Enthusiast

Re: Joining on multiple tables in teradata

Modified query : Please any one help me

Select

a.leg,c.btn,p.prods,svc.sr,speed.test FROM  db1.tb1 as a  

INNER JOIN db1.tb2 as C

   ON a.leg=C.leg

INNER JOIN db1.tb3 as p

   ON a.leg=p.leg

inner join db1.tb3 as svc

on a.leg = svc.leg

inner join db2.tb4 as speed

on a.leg = speed.leg where leg ='xxxx' second query

SELECT a.leg,b.acct_id,c.emp_no,c.emp_name

         FROM        db1.tb1 a,

                                db4.tb1 b,

                               db4.tb5 c

        WHERE     a.leg  = b.sce_acct_id

        AND  b.acct_id = c.acct_id

Now how to join the 2nd query to first query.  

Enthusiast

Re: Joining on multiple tables in teradata

Is this the way u wanted it???

Select

a.leg,c.btn,p.prods,svc.sr,speed.test,b.acct_id,d.emp_no,d.emp_name

FROM  db1.tb1 as a  

INNER JOIN db1.tb2 as C

   ON a.leg=C.leg

INNER JOIN db1.tb3 as p

   ON a.leg=p.leg

inner join db1.tb3 as svc

on a.leg = svc.leg

inner join db2.tb4 as speed

on a.leg = speed.leg 

inner join db4.tb1 as b

on a.leg = b.sce_acct_id

inner join db4.tb5 as d

on b.acct_id = d.acct_id

where leg ='xxxx';

-GK

Enthusiast

Re: Joining on multiple tables in teradata

Hi Inang,

Thank u for ur response,

I already tried this, Data coming out frm this query doesn't match with original.         

I need to pull the data from the db4.tb5 as it does nt have a 'leg' unique index. I need to inculde a table db4.tb1 where sce_acct_id is equal to leg. And db4.tb5 and db4.tb1 has acct_id as unique index.

I hope you understood my problem

Thanks

Vicky 

Enthusiast

Re: Joining on multiple tables in teradata

the table db4.tb1 has   already been included in the query i have written....

Select

a.leg,c.btn,p.prods,svc.sr,speed.test,b.acct_id,d.emp_ no,d.emp_name

FROM  db1.tb1 as a  

INNER JOIN db1.tb2 as C

   ON a.leg=C.leg

INNER JOIN db1.tb3 as p

   ON a.leg=p.leg

inner join db1.tb3 as svc

on a.leg = svc.leg

inner join db2.tb4 as speed

on a.leg = speed.leg 

inner join db4.tb1 as b

on a.leg = b.sce_acct_id

inner join db4.tb5 as d

on b.acct_id = d.acct_id

where leg ='xxxx';