How to write inner join with sub query in SQL Server?

Question

How to make inner join with sub queries in SQL Server?

I have an Orders table with columns orderId, OrderNumber and another table Order transactions with orderId, orderstatuscode columns and orderstatuscode values like 'SHI', 'PAY', 'APQ'.

In Orders transactions table I have multiple record with orderid and orderstatuscode.

I want result among all traction we need orders where it should have PAY transaction but not APQ transaction

Answer 1

You have provided very little information to help you with. Having said that, this could be a good starting point :

SELECT      *
FROM        Orders ord
INNER JOIN  OrderTransactions tra ON ord.OrderId = tra.OrderId
WHERE       tra.OrderStatusCode = 'PAY'
AND         tra.OrderId NOT IN (SELECT      OrderId
                                FROM        OrderTransactions
                                WHERE       OrderStatusCode = 'APQ')

You can check this here : SQL Fiddle