Tricky query

[vramdal]

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
Easy.

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).

[ssystems]

Using MySQL 5.0.67, with InnoDB tables (if that matters).

Yes it does as we can't reliably use counts on innodb tables which was my first impulse by the way. Check this out and just clean it up a bit to optimize performance

select
	t4.id
	,t4.name
	,t4.status
	,t4.year
	,min(t4.year)
  from
    (select
		*
	  from
		(select
			t1.id
			,t1.name
			,t1.status
			,t1.year
			,t2.status delinquency
		  from
			order t1
		  left outer join
			  order t2
			on
			  t1.name = t2.name
			  and t1.year != t2.year
		) t3
	  where
		t3.year = 2009
	) t4
  group by
	t4.name

[vramdal]

Thanks for your suggestion!

However, I get a syntax error on the "from order t1" part - are you sure that's correct?

 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order t1 left outer join order t2 on t1.name = t2.name and t1.year != t2.year' at line 2

[ssystems]

Yes I'm sure about the concept. I forgot to mention avoid using reserved words for your table names, column names, etc. ("Order")

select
	t4.id,
	t4.name,
	t4.status,
	t4.year,
	min(t4.delinquency) delinquency
  from
	(select
		*
	  from
	    (select
			t1.id
			,t1.name
			,t1.status
			,t1.year
			,t2.status delinquency
		  from
			  order_table t1
			left outer join
				order_table t2
			  on
				t1.name = t2.name
				and t1.year != t2.year
		) t3
	  where
		t3.year = 2009
	) t4
  group by
	t4.name;

[vramdal]

Ah, of course, I didn't spot the "order" ambiguancy.
The query seems to work, thanks sooooo much!