Markus,
Monday, March 18, 2002, 1:54:46 PM, you wrote:
ML> There -has- to be an easy solution to this, but I just can't for
ML> the world figure it out.
ML> I have a table called members(id,membernum,firstname,lastname)
ML> and a table called paid(id,membernum,year,paid).
ML> How on earth do I select the members in table members that are
ML> -NOT- in table paid?
ML> The members-table:
ML> mysql> SELECT * FROM members;
ML> +-------+----------+-----------+----------+
ML> | id | membernr | firstname | lastname |
ML> +-------+----------+-----------+----------+
ML> | 00001 | 00001 | markus | lervik |
ML> | 00002 | 00002 | markuas | asdff |
ML> | 00003 | 00003 | test | person |
ML> +-------+----------+-----------+----------+
ML> ...and the paid-table:
ML> +-------+----------+------+------+
ML> | id | membernr | year | paid |
ML> +-------+----------+------+------+
ML> | 00001 | 1 | 2000 | y |
ML> | 00002 | 1 | 2001 | y |
ML> | 00003 | 1 | 2002 | n |
ML> | 00004 | 2 | 2002 | y |
ML> | 00005 | 2 | 2003 | n |
ML> +-------+----------+------+------+
ML> This is what I tried;
ML> SELECT m.membernum,m.firstname,m.lastname,p.year,p.paid
ML> FROM members AS m, paid AS p WHERE (m.membernum != p.membernum)
ML> which really doesn't produce the result I want:
ML> mysql> SELECT M.membernr,M.firstname,M.lastname,P.year,P.paid FROM
ML> members AS M, paid AS B WHERE M.membernr!=P.medlemsnr;
ML> +----------+-----------+----------+------+------+
ML> | membernr | firstname | lastname | year | paid |
ML> +----------+-----------+----------+------+------+
ML> | 00002 | markuas | asdff | 2000 | y |
ML> | 00003 | test | person | 2000 | y |
ML> | 00002 | markuas | asdff | 2001 | y |
ML> | 00003 | test | person | 2001 | y |
ML> | 00002 | markuas | asdff | 2002 | n |
ML> | 00003 | test | person | 2002 | n |
ML> | 00001 | markus | lervik | 2002 | y |
ML> | 00003 | test | person | 2002 | y |
ML> | 00001 | markus | lervik | 2003 | n |
ML> | 00003 | test | person | 2003 | n |
ML> +-----------+-----------+----------+------+-----+
ML> Which is quite logic when I came to think about it.
ML> One could, of course, smack a "AND m.membernr='3' "
ML> on the WHERE-clause, but it becomes a burden later
ML> if the member table grows large.
ML> I'm sure there -must- be a simple way to do this,
ML> and subselects spring to mind.
ML> Perhaps it's just the coffeine that hasn't started
ML> working yet (after about five cups <g>).
What about using JOIN clause?
SELECT members.* FROM members LEFT JOIN paid ON
members.membernr=paid.membernr WHERE paid.membernr IS NULL;
You can read about JOIN in MySQL documentation at:
http://www.mysql.com/doc/J/O/JOIN.html
ML> Cheers,
ML> Markus
--
For technical support contracts, goto https://order.mysql.com/
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
---------------------------------------------------------------------
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
