[Phpwm] SQL Problem

[Dave Brotherstone]

select *
from companies c
where not exists (select 1 from todos t where t.company_id = c.company_id)
and not exists (select 1 from activities a where a.company_id =
c.company_id)


should do the job.   If you're really concerned about performance, you can
also do it by joining the todos and activities to companies, and checking
for null in both the todo_id and activity_id - some DBMSs will do this
quicker than two exists subqueries, but it does depend on all sorts of
things - the size of your datasets, indexes available etc.

Cheers,

Dave.

On Thu, Apr 3, 2008 at 3:57 PM, Daniel Davies <daniel at sitedesign.net> wrote:

>  Hello all,
>
> Sorry, this is not strictly PHP, but its all the same kettle of fish.
>
> I'm working on a system that has companies, and these companies can have
> activities and todos, defined something like:
>
> companies { *company_id*, name, etc... }
> todos { *todo_id*, *company_id*, etc... }
> activities { *activity_id*, *company_id*, etc... }
>
> I'm trying to write a query which will only return companies that do NOT
> have an activity or a todo in one query - my mind is blank! Maybe my brain
> has been rotted by this system, but I can't think of a nice simple way to do
> it. Any ideas? I've had some thoughts about using count, but I'd like to
> check I'm not missing a simple solution before I start writing elaborate
> queries.
>
> Regards,
>
> Daniel
>
>
> _______________________________________________
> Phpwm mailing list
> Phpwm at mailman.lug.org.uk
> https://mailman.lug.org.uk/mailman/listinfo/phpwm
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.lug.org.uk/pipermail/phpwm/attachments/20080403/f6da71cb/attachment.html