Help With Joins

Database

Help With Joins

HI,

Following are my tables:

emp_table
---------
emp_no emp_name address_code
1 aaa 100
2 bbb 200

address_table
-------------
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)
6 REPLIES

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?

Thanks
WAQ
Enthusiast

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.
Senior Apprentice

Re: Help With Joins

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

select
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).

Dieter
WAQ
Enthusiast

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?
Senior Apprentice

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.

Dieter

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.