Hello list, There -has- to be an easy solution to this, but I just can't for the world figure it out.
I have a table called members(id,membernum,firstname,lastname) and a table called paid(id,membernum,year,paid). How on earth do I select the members in table members that are -NOT- in table paid? The members-table: mysql> SELECT * FROM members; +-------+----------+-----------+----------+ | id | membernr | firstname | lastname | +-------+----------+-----------+----------+ | 00001 | 00001 | markus | lervik | | 00002 | 00002 | markuas | asdff | | 00003 | 00003 | test | person | +-------+----------+-----------+----------+ ...and the paid-table: +-------+----------+------+------+ | id | membernr | year | paid | +-------+----------+------+------+ | 00001 | 1 | 2000 | y | | 00002 | 1 | 2001 | y | | 00003 | 1 | 2002 | n | | 00004 | 2 | 2002 | y | | 00005 | 2 | 2003 | n | +-------+----------+------+------+ This is what I tried; SELECT m.membernum,m.firstname,m.lastname,p.year,p.paid FROM members AS m, paid AS p WHERE (m.membernum != p.membernum) which really doesn't produce the result I want: mysql> SELECT M.membernr,M.firstname,M.lastname,P.year,P.paid FROM members AS M, paid AS B WHERE M.membernr!=P.medlemsnr; +----------+-----------+----------+------+------+ | membernr | firstname | lastname | year | paid | +----------+-----------+----------+------+------+ | 00002 | markuas | asdff | 2000 | y | | 00003 | test | person | 2000 | y | | 00002 | markuas | asdff | 2001 | y | | 00003 | test | person | 2001 | y | | 00002 | markuas | asdff | 2002 | n | | 00003 | test | person | 2002 | n | | 00001 | markus | lervik | 2002 | y | | 00003 | test | person | 2002 | y | | 00001 | markus | lervik | 2003 | n | | 00003 | test | person | 2003 | n | +-----------+-----------+----------+------+-----+ Which is quite logic when I came to think about it. One could, of course, smack a "AND m.membernr='3' " on the WHERE-clause, but it becomes a burden later if the member table grows large. I'm sure there -must- be a simple way to do this, and subselects spring to mind. Perhaps it's just the coffeine that hasn't started working yet (after about five cups <g>). Cheers, Markus -- Markus Lervik Linux-administrator with a kungfoo grip Vaasa City Library - Regional Library [EMAIL PROTECTED] +358-6-325 3589 / +358-40-832 6709 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
