Grouping invoices in php mysql delay

Asked

Viewed 168 times

0

Hello, I’m beginner, I’m learning to select, and would like to receive help from this wonderful community.

I need to take only the invoices that are with the a_winning status of each student, would more or less group by id the invoices that are with the status a_winning.

I have a table called invoices with the following fields: id, student id, value, salary and status where each student has multiple invoices, each invoice has the student id, some invoices are paid and others are due, and I wanted to make a select on that table and locate students who have only 1 invoice in arrears.

How would I do that? I appreciate everyone’s help! Thank you

php mysql sql database select

  • The table invoices has the columns id, value and maturity, the status win is stored in which column?

  • Thank you for helping me! I’m sorry, the invoices table has the columns id, value, maturity and status! Thank you

  • Then the value stored in the column status is the word win?

  • So, invoices that have not yet been paid are status a_expire, and paid invoices are status paid.

  • Which fields should be selected? All? from the table invoice same or of students?

  • Make sure the answer is what you need and accept if you’re right.

Show 1 more comment

2 answers

1

To select all columns when the student only has an invoice with the status 'a_winning' it is necessary to use the function group by combined with having.

Follows query:

SELECT id, id_aluno, valor, vencimento, status
FROM faturas
WHERE status = 'a_vencer'
GROUP BY id_aluno
HAVING count(*) = 1

You can see working on this link from SQL Fiddle.

-1

There is no reason to write the name of each column of the table if it will get all. The above query is not wrong, there is only unnecessary redundancy!

Optimized:

SELECT *
FROM faturas
WHERE status = 'a_vencer'
GROUP BY id_aluno
HAVING COUNT(*) = 1;

Browser other questions tagged php mysql sql database select

You are not signed in. Login or sign up in order to post.