IS it possible to inner join 3 tables for SQL?

Question

I'm trying to fetch certain data by using a column that exists in all 3 tables, I have three tables

[Txn].[TxnPaymentResponse]
[Txn].[Txn]
[Txn].[TxnLineItem]

Right now if I run the query, I'm able to get the correct data only userID.

But I want to grab another piece of information (suppose that call column X) from the 3rd table (TxnLineItem). That column doesn't exist in the first 2 tables. In this scenario how could I perform inner join and show that piece of info in the query?

DECLARE  @CompletedTransactionSince  DATETIME2(7) = '2022-09-13 00:00:00.000000'

SELECT DISTINCT
    t.UserID
FROM    
    [Txn].[Txn] T WITH(NOLOCK)
INNER JOIN    
    [Txn].[TxnPaymentResponse] TPR WITH(NOLOCK) ON T.[TxnID] = TPR.[TxnID]
WHERE
    TPR.[PaymentResponseType] = 'FINAL'
    AND TPR.[AuthorizedAmount] > CONVERT(DECIMAL(9,3), 0)
    AND (@CompletedTransactionSince IS NULL OR
         T.[CreatedOn] > @CompletedTransactionSince)

Results from my query:

UserID
C1671FDA-70A8-4C07-BBDF-ACD06ADD145F

Table 3:

TxnID	StandardProductCategory
6FE0D0D0-9959-41AA-9BF0-00000003DED8	Carwash
D1B0EA51-C476-488C-A140-0000C1C7D099	General

Suppose I'm doing inner join like

INNER JOIN    
    [Txn].[TxnLineItem] TXL WITH(NOLOCK) ON T.[TxnID] = [Txn].[TxnID],

But my I want to grab the X column that has the same transactionID. I want to display UserID that has a Carwash only. Not sure if it's possible to write another clause with an inner join.

bekir çelik · Answer

As per "I want to display UserID that has a Carwash only", you need to exclude records.

Just add these EXISTS and NOT EXISTS clauses at the end of to query.

EXISTS part is filter users that has CarWash.

NOT EXISTS part is exclude users that has StandardProduct Category records other than Car Wash

......
AND (@CompletedTransactionSince IS NULL OR
     T.[CreatedOn] > @CompletedTransactionSince)
AND EXISTS ( SELECT 1 FROM [Txn].[TxnLineItem] TXL 
             WHERE T.[TxnID] = [Txn].[TxnID] 
               AND TXL.StandardProductCategory='Carwash')
AND NOT EXISTS( SELECT 1 FROM [Txn].[TxnLineItem] TXL 
                 WHERE T.[TxnID] = [Txn].[TxnID] 
                  AND TXL.StandardProductCategory=<>'Carwash')

IS it possible to inner join 3 tables for SQL?

1 Answers