SQL Select Query Using Three Tables Using Three Tables

Question

I have 3 Tables OrderPayment, OrderSummaryInfo and OrderLossEntity.

OrderPayment table has a column OrdertypeCD, OrderLossEntity table has a column OrderType.

There is no direct relation between all the 3 tables.

Ordersummary has a primary key orderevententity. This table is the master table for both the tables (OrderPayment and OrderLossEntity).

OrderSummaryinfo table has a column called orderNumber and OrderPayment table also has OrderNumber. This OrderNumber is the primary key in OrderPayment table and is referring to OrderSummaryinfo:

OrderSummaryInfo.OrderNumber = OrderPayment.OrderNumber OrderSummaryinfo.orderevententity is the primary key for OrderLosseventEntity.id --> where is the primary key in OrderLosseventEntity table referring to OrderSummaryinfo.orderevententity:

OrderSummaryinfo.orderevententity = OrderLossEntity.id My requirement is OrderTypeCD has 3 possible values "Cash", "Gift" and "rewards".

OrderType column has 2 possible values - "Direct" and "Indirect".

I need a select query from the above tables where OrderType="Direct" and OrderTypeCD="Cash".

Need A Select Query on SQl Or PostgreSql

Answer 1

Here is the query (potsgresql):

SELECT * FROM OrderSummaryInfo /* add rows from two other tables, if needed */
INNER JOIN OrderPayment ON OrderSummaryInfo.OrderNumber = OrderPayment.OrderNumber
INNER JOIN OrderLossEntity ON OrderSummaryInfo.orderevententity = OrderLossEntity.id
WHERE OrderLossEntity.OrderType = 'Direct' AND OrderPayment.OrderTypeCD = 'Cash'

For more info about how to write join queries, refer to the tutorial.