At 6:10 PM -0700 8/30/01, Daren Cotter wrote:
>i have a query that needs to do the following:
>
>in my members table, i store the member id, and the referer id of the member
>that referred them (the referer_id links to the member_id).
>
>for example, a member signs up and gets the id of 0200289, and was referred
>by 0001008. anyway, over time, when members are deleted, the referer field
>is invalid. so if member 0001008 is deleted, member 0200289 has an invalid
>referer_id stored in the db. i need to produce a query that is going to show
>me all accounts w/ an invalid referer id. i am pretty sure this can be done
>by doing a left join from the members table to itself...can anyone help me
>out?
>
>i think this is somewhat close:
>
>select 1.member_id, count(*) as count from members as 1 left join members as
>2 on 1.referer_id = 2.member_id where 2.first_name is not null;
>
>tia,
Sorta, but more like this:
SELECT m1.member_id, m1.referer_id AS 'bad referer'
FROM members AS m1 LEFT JOIN members AS m2 ON m1.referer_id = m2.member_id
WHERE m2.member_id IS NULL
The matches where m2.member_id is NULL are those for which there's
no member record that matches the referer_id.
You can't use numbers as table aliases, by the way.
>
>
>Daren Cotter
>CEO, InboxDollars.com
>http://www.inboxdollars.com
>(507) 382-0435
--
Paul DuBois, [EMAIL PROTECTED]
---------------------------------------------------------------------
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
