probably an easy join

    Date: 01/22/07 (MySQL Communtiy)    Keywords: mysql, sql

    i have two tables, contacts and tasks. users create a contact and then create tasks for the contact. one contact can have many tasks. i want to retrieve the contacts and, if there are any active tasks, i want to retrieve only one. if there are no active tasks, i still want to get the contact. the idea is that users can see a list of contacts, and a little icon appears next to any that have pending task/s. i've been doing this (simplified a bit):

    SELECT c.`id`,c.`name`, t.`id`, t.`task_type` FROM `contacts` AS `c` LEFT JOIN `tasks` AS `t` ON c.`id` = t.`contact_id` AND t.`done` = '0' WHERE 1 GROUP BY c.`id`

    which was working, but lately is running very slow (mysql hangs for 2+ minutes at "copying to tmp table"). is there a more efficient way to do this? everything else i can come up with only returns contacts with active tasks. i had to toss that GROUP BY in there because otherwise i'd get a contact returned as many times as it had tasks, which is obviously not right and a bit hacky. so, i'm sure i'm doing something wrong here.

    Source: http://community.livejournal.com/mysql/108677.html

« Character problems || Updating multiple rows »


antivirus | apache | asp | blogging | browser | bugtracking | cms | crm | css | database | ebay | ecommerce | google | hosting | html | java | jsp | linux | microsoft | mysql | offshore | offshoring | oscommerce | php | postgresql | programming | rss | security | seo | shopping | software | spam | spyware | sql | technology | templates | tracker | virus | web | xml | yahoo | home