On 2007-09-27, Captain Paralytic <paul_lautman@yahoo.com> wrote:
>
> I would also replace your subselect with a LEFT JOIN
>
Thanks. I have always been a bit frightened of LEFT JOINs, but I
decided to give it a try. I was able to come up with a query that
works and looks simpler:

mysql> select p.* from people p left join people s
on s.userid = p.supervisor where (p.category = "affiliated" or
s.category = "affiliated") and p.active = true;

Meanwhile, I also decided to learn about embedded functions.
I produced one which also simplifies the query:

CREATE FUNCTION super_cat (id varchar(20)) RETURNS varchar(20)
begin
declare x,y varchar(20);
select supervisor into x from people where userid = id;
if x is null then
return null;
else
select category into y from people where userid = x;
return y;
end if;
end

The query looks like
select * from people where active = true and (category = "affiliated"
or super_cat(userid) = "affiliated");

The query is quite simple, at the expense of making a specialized
function. Is either approach to be preferred for efficiency?