CODEWITHSUNDEEP
sqldatabase
user17694700
user17694700

Reputation: 1

DVD rental SQL ###### QUERY

Trying to find how many active or inactive rentals there are for a specific store. 1 = active, 0 = inactive. the answer I get doubles the amount of customers and is not right. Can anyone help

 SELECT COUNT(*)
FROM customer, store
WHERE active = 0
AND store_id = 2
ORDER BY customer_id;

Upvotes: 0

Views: 626

Answers (1)

GWR
GWR

Reputation: 209

The join is incorrect. There are no join criteria specified so the result is all possible combinations of customers and stores, filtered by where clause.

Correct way to join is:

...
from customer inner join store on store.store_id = customer.store_id
...

(assuming customer table has the store_id field).

And you probably want to group by store using the group by clause.

Upvotes: 1

Related Questions