Re: is this use of subquery "smart"

Bastien Koert wrote:

On Wed, Feb 4, 2009 at 4:33 PM, Chris <dmagick@gmail.com <mailto:dmagick@gmail.com>> wrote:

        Solution 2:
        SELECT r.reg_id, r.date_registered, r.old_record,
        if(r.ghost_record!=0, (SELECT CONCAT(people.last_name, ', ',
        people.first_name) FROM people, registrants WHERE
        people.instance_id=12 and people.person_id=registrants.person_id
        AND registrants.reg_id=r.ghost_record), 'y') as registered_by_name
        FROM registrants r
        WHERE r.org_id=12
        AND r.reg_status=0
        AND r.reg_id=r.person_id

        this way subquery will be executed "everytime", but I have
        everything on one place?

    Because it has to evaluate the whole 'if' statement (including your
    subquery) for each row in the result set.

    Best place to ask would be on the mysql mailing list:

    http://lists.mysql.com/

try a case when then statement.

Same problem - the case is evaluated (including the subquery) with every row in the result. It could be done as a join I guess but I'll leave that as an exercise for the OP. -- Postgresql & php tutorials http://www.designmagick.com/