List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:August 30 2001 11:55pm
Subject:Re: tough query
View as plain text  
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.

>Daren Cotter
>(507) 382-0435

Paul DuBois, paul@stripped
tough queryDaren Cotter31 Aug
  • Re: tough queryPaul DuBois31 Aug