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
>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;
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.
Paul DuBois, paul@stripped
|• tough query||Daren Cotter||31 Aug|
| • Re: tough query||Paul DuBois||31 Aug|