CODEWITHSUNDEEP
mysql
Matt
Matt

Reputation: 11317

Single MySQL query which checks another table for rows

I have a Users table and a Payments table. I need a query which lists the users who DO NOT have a record in the payments table where the field PaymentCompleted=1.

These are the columns in the tables (simplified):

     Users: UserID, UserName
  Payments: PaymentID, UserID, PaymentCompleted

The query should select the field UserName.

Upvotes: 0

Views: 71

Answers (3)

Dan Breen
Dan Breen

Reputation: 12924

select distinct UserName
from Users left outer join Payments on Users.UserID = Payments.UserID
where PaymentCompleted is NULL or PaymentCompleted != 1

Upvotes: 2

Jason
Jason

Reputation: 4130

select * from t_users T where T.userid not exists (select p.userid from t_payments t where PaymentCompleted=1).

One note: "not in" clauses can be computationally inefficient for large numbers of records. If you start seeing performance issues, you may want to do some refactoring/redesign.

Upvotes: 0

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25370

  SELECT UserName
  FROM Users u
  WHERE NOT EXISTS(Select 1 
                   from Payments p 
                   Where p.UserId = u.UserId
                   AND p.PaymentCompleted = 1)

Upvotes: 2

Related Questions