Help With Joins


Help With Joins


Following are my tables:

emp_no emp_name address_code
1 aaa 100
2 bbb 200

address_code address_type hno city pincode
100 home 133 abc 123456
100 office 433 kbc 786989
200 home 991 mkn 847474
200 office 874 mkn 847474

I want a query which gives output in one row:

emp_no emp_name address_code address_type hno city pincode address_type hno city pincode
1 aaa 100 home 133 abc 123456 office 433 kbc 786989
2 bbb 200 home 991 mkn 847474 office 874 mkn 847474

One way is, we can create 2 subtables in the from clause.
Is there any better way? Can anyone suggest.

Advanced Thanks
Tags (1)

Re: Help With Joins

Please let me know if I need to keep any flags in any table(EMP or ADDRESS) to fetch the data.

I tried in many ways...but didn't get the required output.

Creating derived tables is the only option?


Re: Help With Joins

Do you really want to do it with SQL? This is a presentation level function, so it should be handled by your tool use for presentation.
Junior Contributor

Re: Help With Joins

As you already wrote you need to join the same table twice:

from emp_table as e
left join address_table as home
on e.address_code = home.address_code
and address_type = 'home'
left join address_table as office
on e.address_code = office.address_code
and address_type = 'office'

You could return the same result using some MIN(CASE...) or OLAP functions, but the join is pürobably the most efficient (in this case).


Re: Help With Joins

But in that case it won't be a dynamic query eg if you have a third record against 100 in address_table then it won't fetch it using two left joins, rather for that you need to use three left joins.

I think in Teradata we can not directly implement crosstab queries using direct SQL. Am I right dnoeth?
Junior Contributor

Re: Help With Joins

The data provided looked like preknown values/number of values, for this it's the right query.
If the values are unknown, but maximum number of rows per value is low/known and you just need a kind of repeating group, it's possible, too, e.g. with OLAP functions.

But if you look for a real crosstab, there's no PIVOT in Teradata, yet.
Of course you could write a Stored Procedure which builts the neccessary SQL and returns a crosstab, i've done that looong years ago in MS SQL Server.


Re: Help With Joins

Thanks dnoeth and WAQ for your reply.
I have the query but thought of trying it differently.

Thanks for you time.