issue with joins

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

issue with joins

Hi All,

 

I am joining a piece of code B  to my  big code A as a left outer join on sepcial_id.  But i am getting numbers wrong. what are possible solutions to narrow down the issues.

 

sepcial_id is common id between both tables and that id is unique.

and there are no extra special id is there iN piece of code B .That id's are subset of code A.

Please help me out if there any suggerstions .

 

 

thanks

4 REPLIES
Junior Contributor

Re: issue with joins

"getting numbers wrong" = wrong number of rows in your result set, i.e. missing rows?

 

This is usually caused by a condition on the inner table in WHERE, which is changing the join to an Inner join (check Explain).

The basic rule of thumb (which is correct in 99%):

 

  • conditons on the Outer table are placed in WHERE
  • conditons on the Inner table are moved to ON

 

Enthusiast

Re: issue with joins

Here goes the  issue

select serial_num ,Pref_Cd_Id from Prefcode   where serial_num = 4342562876911123

This is table B

serial_num                            Pref_Cd_Id
5,342,562,876,911123               1
5,342,562,876911123                 2

 

 

table A as only 1  serial_num   5,342,562,876,911123

when we are doing left outer join with A and B  we are getting numbers more.

 

I cant remove duplicates since the count pref_cd_id wil  come down. Its independenet numbers.

 

Any idea how to build the logic.

 

Junior Contributor

Re: issue with joins

If you don't want multiple rows joined you must apply some logic to reduce it to a single row before the join, either some aggregation or some condition which returns a unique row (e.g. based on the Primary Key or ROW_NUMBER etc).

Re: issue with joins

if you want to restrict your result to a single record. you need to put some filter condition in your query, may be on Pref_Cd_Id columns.