CODEWITHSUNDEEP
postgresql
MDaud
MDaud

Reputation: 1

Why does the below code not discount any with amount below 9.99

The below code does not follow the subquery as I am looking to see the people with amount above 9.99. 

select customer_id,first_name
from customer
join payment using (customer_id)
where customer_id in(select customer_id from payment where amount > 9.99)
order by customer_id

If I do not use join payment using (customer_id) then it works but I want to see amount which is only in the payment table.

Upvotes: 0

Views: 41

Answers (1)

forpas
forpas

Reputation: 164174

In your current query you do a useless join of customer to payment because all you need is the WHERE clause:

select customer_id, first_name
from customer
where customer_id in (select customer_id from payment where amount > 9.99)
order by customer_id

If this (like your query) does not return what you need, maybe it is because you want the customer_ids of customers who made payments > 9.99 only.
To get these customers you must modify the subquery after IN like this:

select customer_id, first_name
from customer
where customer_id in (
  select customer_id 
  from payment 
  group by customer_id
  having min(amount) > 9.99
)
order by customer_id

This subquery returns customer_ids of customers whose minimum payment is greater than 9.99.

Upvotes: 1

Related Questions