CODEWITHSUNDEEP
sqlsql-servert-sqljoin
cool breeze
cool breeze

Reputation: 4811

Joining on a table but only match if it has at least one row based on a condition

I'm trying to find all users who have at least 1 transaction that has the StoreLocationID=123.

The basic query to get the count of users is:

SELECT COUNT(*) 
FROM Users u

The transaction table looks like:

Transactions
- ID
- UserID
- Amount
- Date
- StoreLocationID

How can I find ALL users who have at least 1 transaction where StoreLocationID=123.

I can join on the table, but I just need to know if there is at least 1 row with StoreLocationID=123.

Upvotes: 1

Views: 1050

Answers (1)

GMB
GMB

Reputation: 222402

You can use a correlated subquery with an exists condition:

select * 
from users u
where exists (
    select 1
    from transactions t
    where t.userID = u.userID
    and t.StoreLocationID = 123
)

This will give you all users that have at least one transaction on in store 123.

If you just want to count of such users, then:

select count(*) 
from users u
where exists (
    select 1
    from transactions t
    where t.userID = u.userID
    and t.StoreLocationID = 123
)

Or:

select count(distinct userID) from transactions where StoreLocationID = 123

Upvotes: 5

Related Questions