php - getting all the records in the table

348

i wanted to retrieve all the records whose "amount" is greater than or equal to 1500. The problem is even if the "amount" is less than 1500 it will be also displayed in the page.

customers table
id   name
1    sample
2    sample2
3    sample3


payments table

p_id  amount  id(foreign key)
1      800     2
2      800     2
3      1500    1
4      1200    3

costumer 1 and 2 should be retrieved because the amount>= 1500.

Thank you, Mick :)

Answer

Solution:

This requires to join the tables.GROUP BY is used since one of the columns is being aggregated usingSUM() and theHAVING clause is used to filter the result of aggregation.

SELECT  a.ID, a.name
FROM    customers a
        INNER JOIN payments b
            ON a.ID = b.id
GROUP   BY a.ID, a.name
HAVING  SUM(b.amount) >= 1500

SQLFiddle Demo

People are also looking for solutions to the problem: php - How to retrieve all records after one with an ID in symfony?

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question