I have a table 'order' with the following columns:
id | name | status | year
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.
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:
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
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.
Now, I want a query that gives me the following result:
1 | John Doe | 0 | 2009 | 0
2 | Dunc Doe | 0 | 2009 | 1
3 | Jack Doe | 0 | 2009 | 1
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.
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).