select customer_id,first_name,last_name,rental.rental_id,film_id,title,
replacement_cost,rental_date,return_date,amount
from customer
join rental using (customer_id)
join inventory using (inventory_id)
join film using (film_id)
join payment using (customer_id)
where customer_id in (select customer_id from payment where amount>9.99)
order by customer_id
Rough guess - The customers returned by the subquery also had transactions where the payment was less than 9.99?
@ThomasRushton they do but surely with the
where customer_id in (select customer_id from payment where amount>9.99)
it should take out any below 9.99?
No. What it's doing is returning all the data, but only for those customers who have at any point spent over 9.99.
What might work is changing that WHERE clause to "where customer_id not in (select customer_id from payment where amount <= 9.99)"
Hi it still does not work. Only way I can think is by doing a join with the payment table then I do not need to use a subquery
Yep I am looking to only see transactions over 9.99
In that case, what's wrong with the where clause "where payment.amount > 9.99"?
17 People are following this question.
