Hello Markus, > 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>).
Try using an OUTER JOIN (equi-join) then exclude all matched rows by phrasing the WHERE clause as P.membernr IS NULL ie there is no equivalent P table row. BTW typo: AS P not AS B Regards, =dn --------------------------------------------------------------------- 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
