CODEWITHSUNDEEP
sqloracle-database
Prashanth
Prashanth

Reputation: 95

Dynamic column in Oracle SQL select

I have three tables:

T_ORDER_PLACEMENTS (ORDER_ID, CUSTOMER_ID, ORDER_DATE)
T_ORDER_DETAILS (ORDER_ID, STOCK_ID)
T_STOCK_DETAILS(STOCK_ID, STOCK_NAME, STOCK_PRICE)

Can someone please help me to write a query which generates the following output:

STOCK_ID, STOCK_NAME, STOCK_PRICE, ORDERED_STATUS
1         stock1      5000         ordered
2         stock2      10000        unordered

Populate the ORDERED_STATUS column with 'ordered' if the stock is ordered and 'unordered' if the stock is unordered.

Upvotes: 1

Views: 46

Answers (1)

MatBailie
MatBailie

Reputation: 86706

SELECT
    t_stock_details.*,
    CASE WHEN order_check.stock_id IS NULL THEN 'unordered' ELSE 'ordered' END  AS ordered_status
FROM
    t_stock_details
LEFT JOIN
(
    SELECT stock_id FROM t_order_details GROUP BY stock_id
)
    order_check
        ON order_check.stock_id = t_stock_details.stock_id

The sub-query checks to see which stock_ids have an order associated with them. It also uses GROUP BY to ensure only one row is returned per stock_id, no matter how many orders are found.

The LEFT JOIN ensures that every row in t_stock_details is returned, whether or not it is successfully joined to anything. Where there is a successful join, we know there has been an order. It will also only ever be joined on to one row at the most (thanks to the above mentioned GROUP BY, so no duplication is being caused).

An unsuccessful join will have NULL in the order_check.stock_id, so we use that to check which string to return, using a CASE statement.

Upvotes: 1

Related Questions