question

MDAUD avatar image
MDAUD asked

when i run the below query it shows me the the people with an amount less than 9.99. why is this?

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

subquery
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rough guess - The customers returned by the subquery also had transactions where the payment was less than 9.99?

0 Likes 0 ·

@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?

0 Likes 0 ·

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)"

0 Likes 0 ·
Show more comments
MDAUD avatar image
MDAUD answered

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

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

MDAUD avatar image
MDAUD answered

Yep I am looking to only see transactions over 9.99

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

In that case, what's wrong with the where clause "where payment.amount > 9.99"?

1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.