First of all SORRY FOR LONG POST!!
Ok, from what I understand you have 2 tables.
In Table1 (members) you have member_id, name and adress.
In Table2 (payment) you have payment_id, payment_member_id, payment_date.
This is what I guess atleast.
So I made some experimenting with this.
CREATE TABLE members (
member_id int(11) NOT NULL auto_increment,
member_name varchar(255) default NULL,
member_adress varchar(255) default NULL,
PRIMARY KEY (member_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=323 ;
CREATE TABLE payment (
payment_id int(11) NOT NULL auto_increment,
payment_member_id int(11) NOT NULL,
payment_date date default NULL,
PRIMARY KEY (payment_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=323 ;
INSERT INTO members (member_name, member_adress) VALUES ('A A', 'a');
INSERT INTO members (member_name, member_adress) VALUES ('B B', 'b');
INSERT INTO members (member_name, member_adress) VALUES ('C C', 'c');
INSERT INTO members (member_name, member_adress) VALUES ('D D', 'd');
INSERT INTO members (member_name, member_adress) VALUES ('E E', 'e');
INSERT INTO members (member_name, member_adress) VALUES ('F F', 'f');
INSERT INTO members (member_name, member_adress) VALUES ('G G', 'g');
INSERT INTO members (member_name, member_adress) VALUES ('H H', 'h');
INSERT INTO members (member_name, member_adress) VALUES ('I I', 'i');
INSERT INTO members (member_name, member_adress) VALUES ('J J', 'j');
INSERT INTO members (member_name, member_adress) VALUES ('K K', 'k');
INSERT INTO members (member_name, member_adress) VALUES ('L L', 'l');
INSERT INTO members (member_name, member_adress) VALUES ('M M', 'm');
INSERT INTO members (member_name, member_adress) VALUES ('N N', 'n');
INSERT INTO members (member_name, member_adress) VALUES ('O O', 'o');
INSERT INTO members (member_name, member_adress) VALUES ('P P', 'p');
INSERT INTO members (member_name, member_adress) VALUES ('Q Q', 'q');
INSERT INTO members (member_name, member_adress) VALUES ('R R', 'r');
INSERT INTO members (member_name, member_adress) VALUES ('S S', 's');
INSERT INTO members (member_name, member_adress) VALUES ('T T', 't');
INSERT INTO members (member_name, member_adress) VALUES ('U U', 'u');
INSERT INTO members (member_name, member_adress) VALUES ('V V', 'v');
INSERT INTO members (member_name, member_adress) VALUES ('W W', 'w');
INSERT INTO members (member_name, member_adress) VALUES ('X X', 'x');
INSERT INTO members (member_name, member_adress) VALUES ('Z Z', 'z');
Now I had to check what id's the members got :
mysql> SELECT * FROM members;
+-----------+-------------+---------------+
| member_id | member_name | member_adress |
+-----------+-------------+---------------+
| 323 | A A | a |
| 324 | B B | b |
| 325 | C C | c |
| 326 | D D | d |
| 327 | E E | e |
| 328 | F F | f |
| 329 | G G | g |
| 330 | H H | h |
| 331 | I I | i |
| 332 | J J | j |
| 333 | K K | k |
| 334 | L L | l |
| 335 | M M | m |
| 336 | N N | n |
| 337 | O O | o |
| 338 | P P | p |
| 339 | Q Q | q |
| 340 | R R | r |
| 341 | S S | s |
| 342 | T T | t |
| 343 | U U | u |
| 344 | V V | v |
| 345 | W W | w |
| 346 | X X | x |
| 347 | Z Z | z |
+-----------+-------------+---------------+
25 rows in set (0.01 sec)
Okey.... they got from 323-347... ok, lets add some payment records for them then.
INSERT INTO payment (payment_member_id, payment_date) VALUES (323, '2008-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (326, '2008-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (327, '2008-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (328, '2008-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (331, '2008-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (332, '2008-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (333, '2008-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (334, '2008-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (335, '2008-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (336, '2008-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (337, '2008-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (338, '2008-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (339, '2008-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (340, '2008-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (341, '2008-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (342, '2008-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (343, '2008-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (344, '2008-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (345, '2008-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (346, '2008-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (347, '2008-05-24');
Those where the ones that payed in 2008 (all on the same date, how nice :P)
And lets add those who payed in 2009 as well (oh look at that, same date as well)
INSERT INTO payment (payment_member_id, payment_date) VALUES (324, '2009-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (326, '2009-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (327, '2009-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (328, '2009-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (329, '2009-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (331, '2009-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (332, '2009-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (333, '2009-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (334, '2009-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (335, '2009-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (336, '2009-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (337, '2009-05-24');
INSERT INTO payment (payment_member_id, payment_date) VALUES (338, '2009-05-24');
Yes, I'm a lazy bum and didn't want to add a bunch of unique dates....
Oh well, on with the experiments.
mysql> SELECT member_name, payment_date
-> FROM members
-> JOIN payment
-> WHERE member_id = payment_member_id
-> ORDER BY member_id ASC, payment_date ASC;
+-------------+--------------+
| member_name | payment_date |
+-------------+--------------+
| A A | 2008-05-24 |
| B B | 2009-05-24 |
| D D | 2008-05-24 |
| D D | 2009-05-24 |
| E E | 2008-05-24 |
| E E | 2009-05-24 |
| F F | 2008-05-24 |
| F F | 2009-05-24 |
| G G | 2009-05-24 |
| I I | 2008-05-24 |
| I I | 2009-05-24 |
| J J | 2008-05-24 |
| J J | 2009-05-24 |
| K K | 2008-05-24 |
| K K | 2009-05-24 |
| L L | 2008-05-24 |
| L L | 2009-05-24 |
| M M | 2008-05-24 |
| M M | 2009-05-24 |
| N N | 2008-05-24 |
| N N | 2009-05-24 |
| O O | 2008-05-24 |
| O O | 2009-05-24 |
| P P | 2008-05-24 |
| P P | 2009-05-24 |
| Q Q | 2008-05-24 |
| R R | 2008-05-24 |
| S S | 2008-05-24 |
| T T | 2008-05-24 |
| U U | 2008-05-24 |
| V V | 2008-05-24 |
| W W | 2008-05-24 |
| X X | 2008-05-24 |
| Z Z | 2008-05-24 |
+-------------+--------------+
34 rows in set (0.00 sec)
For example A payed in 2008 but not in 2009, B payed in 2009 but not in 2008, C didn't pay either year (what a bastard), D payed both 2008 and 2009 and so on.
Ok, now lets see who payed in 2008.
mysql> SELECT member_name, payment_date
-> FROM members
-> JOIN payment
-> WHERE member_id = payment_member_id AND
-> YEAR(payment_date)='2008'
-> ORDER BY member_id ASC;
+-------------+--------------+
| member_name | payment_date |
+-------------+--------------+
| A A | 2008-05-24 |
| D D | 2008-05-24 |
| E E | 2008-05-24 |
| F F | 2008-05-24 |
| I I | 2008-05-24 |
| J J | 2008-05-24 |
| K K | 2008-05-24 |
| L L | 2008-05-24 |
| M M | 2008-05-24 |
| N N | 2008-05-24 |
| O O | 2008-05-24 |
| P P | 2008-05-24 |
| Q Q | 2008-05-24 |
| R R | 2008-05-24 |
| S S | 2008-05-24 |
| T T | 2008-05-24 |
| U U | 2008-05-24 |
| V V | 2008-05-24 |
| W W | 2008-05-24 |
| X X | 2008-05-24 |
| Z Z | 2008-05-24 |
+-------------+--------------+
21 rows in set (0.00 sec)
Ok, so far so good.
Now lets see who payed both 2008 AND 2009.
mysql> SELECT m1.member_name, p1.payment_date, p2.payment_date
-> FROM members AS m1
-> JOIN payment AS p1
-> ON m1.member_id = p1.payment_member_id AND YEAR(p1.payment_date)='2008'
-> JOIN payment AS p2
-> ON m1.member_id = p2.payment_member_id AND YEAR(p2.payment_date)='2009'
-> ORDER BY m1.member_id ASC;
+-------------+--------------+--------------+
| member_name | payment_date | payment_date |
+-------------+--------------+--------------+
| D D | 2008-05-24 | 2009-05-24 |
| E E | 2008-05-24 | 2009-05-24 |
| F F | 2008-05-24 | 2009-05-24 |
| I I | 2008-05-24 | 2009-05-24 |
| J J | 2008-05-24 | 2009-05-24 |
| K K | 2008-05-24 | 2009-05-24 |
| L L | 2008-05-24 | 2009-05-24 |
| M M | 2008-05-24 | 2009-05-24 |
| N N | 2008-05-24 | 2009-05-24 |
| O O | 2008-05-24 | 2009-05-24 |
| P P | 2008-05-24 | 2009-05-24 |
+-------------+--------------+--------------+
11 rows in set (0.02 sec)
Ok, those are the good members, but that's not the info you wanted so lets change the JOINs into LEFT JOINs and see what happends.
mysql> SELECT m1.member_name, p1.payment_date, p2.payment_date
-> FROM members AS m1
-> LEFT JOIN payment AS p1
-> ON m1.member_id = p1.payment_member_id AND YEAR(p1.payment_date)='2008'
-> LEFT JOIN payment AS p2
-> ON m1.member_id = p2.payment_member_id AND YEAR(p2.payment_date)='2009'
-> ORDER BY m1.member_id ASC;
+-------------+--------------+--------------+
| member_name | payment_date | payment_date |
+-------------+--------------+--------------+
| A A | 2008-05-24 | NULL |
| B B | NULL | 2009-05-24 |
| C C | NULL | NULL |
| D D | 2008-05-24 | 2009-05-24 |
| E E | 2008-05-24 | 2009-05-24 |
| F F | 2008-05-24 | 2009-05-24 |
| G G | NULL | 2009-05-24 |
| H H | NULL | NULL |
| I I | 2008-05-24 | 2009-05-24 |
| J J | 2008-05-24 | 2009-05-24 |
| K K | 2008-05-24 | 2009-05-24 |
| L L | 2008-05-24 | 2009-05-24 |
| M M | 2008-05-24 | 2009-05-24 |
| N N | 2008-05-24 | 2009-05-24 |
| O O | 2008-05-24 | 2009-05-24 |
| P P | 2008-05-24 | 2009-05-24 |
| Q Q | 2008-05-24 | NULL |
| R R | 2008-05-24 | NULL |
| S S | 2008-05-24 | NULL |
| T T | 2008-05-24 | NULL |
| U U | 2008-05-24 | NULL |
| V V | 2008-05-24 | NULL |
| W W | 2008-05-24 | NULL |
| X X | 2008-05-24 | NULL |
| Z Z | 2008-05-24 | NULL |
+-------------+--------------+--------------+
25 rows in set (0.00 sec)
Now where are geting somewhere :)
However, you wheren't interested in who has payed in 2009, only those bad members who didn't, so lets add a WHERE to the mix.
mysql> SELECT m1.member_name, p1.payment_date, p2.payment_date
-> FROM members AS m1
-> LEFT JOIN payment AS p1
-> ON m1.member_id = p1.payment_member_id AND YEAR(p1.payment_date)='2008'
-> LEFT JOIN payment AS p2
-> ON m1.member_id = p2.payment_member_id AND YEAR(p2.payment_date)='2009'
-> WHERE p2.payment_date IS NULL
-> ORDER BY m1.member_id ASC;
+-------------+--------------+--------------+
| member_name | payment_date | payment_date |
+-------------+--------------+--------------+
| A A | 2008-05-24 | NULL |
| C C | NULL | NULL |
| H H | NULL | NULL |
| Q Q | 2008-05-24 | NULL |
| R R | 2008-05-24 | NULL |
| S S | 2008-05-24 | NULL |
| T T | 2008-05-24 | NULL |
| U U | 2008-05-24 | NULL |
| V V | 2008-05-24 | NULL |
| W W | 2008-05-24 | NULL |
| X X | 2008-05-24 | NULL |
| Z Z | 2008-05-24 | NULL |
+-------------+--------------+--------------+
12 rows in set (0.00 sec)
Now we have everyone that haven't payed in 2009, however, we still have 2 members who didn't pay in 2008 either, now it's 2 ways of doing this, either we add a AND to there WHERE clause where p1.payment_date IS NOT NULL or you change the first LEFT JOIN (p1) to a normal JOIN makeing it only add those of 2008 skipping all the NULL right there.
Personally I like the first sollution better as for me personally it makes the code look better (however it's just my personal opinion :) )
So....
mysql> SELECT m1.member_name, p1.payment_date, p2.payment_date
-> FROM members AS m1
-> LEFT JOIN payment AS p1
-> ON m1.member_id = p1.payment_member_id AND YEAR(p1.payment_date)='2008'
-> LEFT JOIN payment AS p2
-> ON m1.member_id = p2.payment_member_id AND YEAR(p2.payment_date)='2009'
-> WHERE p2.payment_date IS NULL AND p1.payment_date IS NOT NULL
-> ORDER BY m1.member_id ASC;
+-------------+--------------+--------------+
| member_name | payment_date | payment_date |
+-------------+--------------+--------------+
| A A | 2008-05-24 | NULL |
| Q Q | 2008-05-24 | NULL |
| R R | 2008-05-24 | NULL |
| S S | 2008-05-24 | NULL |
| T T | 2008-05-24 | NULL |
| U U | 2008-05-24 | NULL |
| V V | 2008-05-24 | NULL |
| W W | 2008-05-24 | NULL |
| X X | 2008-05-24 | NULL |
| Z Z | 2008-05-24 | NULL |
+-------------+--------------+--------------+
10 rows in set (0.00 sec)
And now we have the members that has payed in 2008 but not in 2009.
Again sorry for the long post, and for explaining what Peter Brawley allready have, but I wanted to make an experiment for myself to try this as I'm still learning MySQL myself and I thought this looked like a fun problem :)