I have a table 'order' with the following columns:
For simplicity, let's say the table contains people who attended an annual event. The 'status' column is '1' if the attendant has paid his invoice for the event, and '0' if he has not paid yet.
id | name | status | year
I currently have a query listing all attendants from a given year:
select * from order where year = 2009
Now, what's not so easy:
I want to add a column to query result, which should tell me if the attendant has unpaid invoices for previous events.
Let's say the table contains the following data:
This means that John, Dunc and Jack all have signed up for the 2009 event. No-one has paid yet. John and Jane attended last year's event (2008), and are all paid up. Jack, however, did never pay for 2008. Dunc even has an unpaid bill from the 2007 event.
1 | John Doe | 0 | 2009
2 | Dunc Doe | 0 | 2009
3 | Jack Doe | 0 | 2009
4 | John Doe | 1 | 2008
5 | Jane Doe | 1 | 2008
6 | Jack Doe | 0 | 2008
7 | Dunc Doe | 0 | 2007
Now, I want a query that gives me the following result:
Column 5 would be 1 if the attendant has paid for all his previous events, and 0 if he has unpaid bills from previous events. Wether he has paid for this year's event (2009) is irrelevant.
1 | John Doe | 0 | 2009 | 0
2 | Dunc Doe | 0 | 2009 | 1
3 | Jack Doe | 0 | 2009 | 1
Thus, Dunc and Jack gets a '0' because they have unpaid bills from 2007 and 2008, respectively.
What should this SQL query look like?
Using MySQL 5.0.67, with InnoDB tables (if that matters).
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Tags for this Thread