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 ·
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.