CODEWITHSUNDEEP
mysqlsql
TuK
TuK

Reputation: 3556

Struggling with a SQL Query

I've got a table called transaction

Transaction:
  User_Id: int
  Transaction_Type: int
  Amount: int

How would I query all users that do not have transaction type 1. 

SELECT * FROM Transaction WHERE Transaction_Type <> 1 Group By User_Id

seems to return all users & their transaction types that are not = to 1. I need all users that are missing / have no record of type 1.

Upvotes: 0

Views: 66

Answers (6)

Marcus Adams
Marcus Adams

Reputation: 53880

I'd do this with a JOIN. Assuming a Users table:

SELECT u.*
FROM Users u
LEFT JOIN Transaction t
ON t.User_ID = u.User_ID
AND t.Transaction_Type = 1
WHERE t.User_ID IS NULL

This method avoids the subquery and DISTINCT.

Upvotes: 0

biziclop
biziclop

Reputation: 14626

SELECT *
FROM    ( SELECT DISTINCT User_Id FROM Transaction ) AS User
LEFT JOIN Transaction ON Transaction.User_Id         = User.id
                     AND Transaction.Transaction_Id != 1
WHERE Transaction.Transaction_Id IS NULL
GROUP BY User.id

Upvotes: 1

voidengine
voidengine

Reputation: 2579

For example

SELECT DISTINCT User_Id
FROM [Transaction]
EXCEPT
SELECT User_Id
FROM [Transaction]
WHERE Transaction_Id = 1

That's if you don't have a separate table for users. If you do, then use an outer join.

I would suggest not using keywords (like Transaction) as table/column names

Upvotes: 0

Borealid
Borealid

Reputation: 98559

SELECT DISTINCT User_Id
FROM Transaction
WHERE Transaction_Id <> 1
GROUP BY User_Id

This gets each User_Id which has at least one transaction which is not of type 1. If you also want the ones with no transactions, add OR Transaction_Id IS NULL to the WHERE statement.

Upvotes: 0

Anthony Grist
Anthony Grist

Reputation: 38345

There's probably a more elegant way of doing this, but this should work:

SELECT * FROM Transaction WHERE User_Id NOT IN (SELECT User_Id FROM Transaction WHERE Transaction_Type = 1)

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270775

This can be done with an IN() subquery

SELECT DISTINCT User_Id FROM Transaction
WHERE User_Id NOT IN (SELECT DISTINCT User_Id FROM Transaction WHERE Transaction_Type = 1)

Or with a NOT EXISTS

SELECT
  DISTINCT User_Id 
FROM Transaction t
WHERE
  NOT EXISTS (SELECT User_Id FROM Transaction tn WHERE Transaction_Type = 1) 
  AND t.User_Id = tn.User_Id

Upvotes: 4

Related Questions