CODEWITHSUNDEEP
sqlsql-server-2012
cdalto
cdalto

Reputation: 857

SQL Query Not Displaying as Expected

I'm trying to find where customers have not made any transactions, by joining two tables Customer and Transaction. However, I get no values returned with my query.

SELECT
[CUST].[CustID],
[CUST].[CustName]
FROM [Cust]
INNER JOIN [TRANS]
ON [CUST].[CustID] = [TRANS].[CustID]
WHERE [CUST].[CustID] != [TRANS].[CustID]

Upvotes: 0

Views: 44

Answers (2)

Filipe Silva
Filipe Silva

Reputation: 21657

What you are doing is the same as doing:

SELECT [CUST].[CustID],
       [CUST].[CustName]
FROM [Cust]
INNER JOIN [TRANS]
    ON [CUST].[CustID] = [TRANS].[CustID] AND [CUST].[CustID] != [TRANS].[CustID]

This will obviously never happen, hence the result with no rows.

To get the customers with no transactions you should do:

SELECT [CUST].[CustID],
       [CUST].[CustName]
FROM [Cust]
WHERE NOT EXISTS (SELECT 1 
                  FROM [TRANS]
                  WHERE [CUST].[CustID] = [TRANS].[CustID]
                 )

Upvotes: 2

giammin
giammin

Reputation: 18958

you are joining on

[CUST].[CustID] = [TRANS].[CustID]

but the where clause says 

[CUST].[CustID] != [TRANS].[CustID]

if you want the list of costumers with no transaction you should use LEFT JOIN:

SELECT
[CUST].[CustID],
[CUST].[CustName]
FROM [Cust]
LEFT JOIN [TRANS]
ON [CUST].[CustID] = [TRANS].[CustID]
WHERE [TRANS].[CustID] is null

Upvotes: 1

Related Questions