is this use of subquery "smart"
| From: | Afan Pasalic | Date: | Wed, 04 Feb 2009 18:35:44 +0000 |
| Subject: | is this use of subquery "smart" | ||
| Groups: | php.general | ||
| Request: | Send a blank email to php-general+get-287625@lists.php.net to get a copy of this message | ||
What would be more "appropriate" way to create a query:
Solution 1:
select records from registrants table
SELECT r.reg_id, r.date_registered, r.old_record
FROM registrants r
WHERE r.org_id=12
AND r.reg_status=0
AND r.reg_id=r.person_id
# php "validation"
if($old_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
}
this way 2nd query will be executed only if old_record is zero (let's say 10% - 20% of all records)
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?
thanks for any opinion...
afan