select value from different tables

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.
Scout

select value from different tables

 

My question(tera data) is "which product bear lowest shipment cost".I have 2 following tables

       product table               shipmethod table
      productid       name            shipmethodid  shiprate
        1             abc                1             2     
        2             def                2             4  

but product table has no relationship with shipmethod table directly.however there is indirect relationship through 2 following tables

SalesOrderDetail                             SalesOrderHeader
id   productid    salesorderid           id     salesorderid    shipmethodid
1     1                 1                 1         1                1
2     2                 2                 2         2                2   

i have used inner join to select data from different tables

select  pr.name , sm.shiprate from Dice_RDM.Product pr
inner join Dice_RDM.SalesOrderDetail so
on so.productid = pr.productid
inner join Dice_RDM.SalesOrderHeader sh
on sh.SalesOrderID = so.salesorderid
inner join Dice_RDM.ShipMethod sm
on sm.ShipMethodID=sh.ShipMethodID
QUALIFY ROW_NUMBER() OVER (ORDER BY sm.ShipRate) <=1;

i am getting values 
name        shiprate
abc          2

is this right approach ?

1 REPLY 1
Teradata Employee

Re: select value from different tables

Hi saqib,

 

Yes, this is the right approach - joins are more than OK in a database.

Anyway, the question feels incomplete.

 

Let say you have multiple products with multiple shipments, maybe you want to check either the average rate or the total rate ?

Maybe you want also the best difference between the product price and the shipment rate ?