[Phpwm] SQL Problem

[Daniel Davies]

Thanks for the advice folks. Speed to get this up and running is far 
more important than getting performance right now, this only needs to 
sit in a cron that runs once a day.

    SELECT `c`.`name`, `u`.`user_id`, `c`.`company_id`
    FROM `companies` AS `c`
    JOIN `acl_users` AS `u`
        ON `u`.`user_id` = `c`.`owner_id`
    WHERE NOT
    EXISTS (
        SELECT 1
        FROM `activities` AS `a`
        WHERE `a`.`company_id` = `c`.`company_id`
        AND `a`.`activity_start` < :time_since
    )
    AND `c`.`company_type_id` = '2'

Worked just fine =). I went with the where not exists solution because 
it seemed the easiest way of adding the other requirements I had.

Thank you again for the input,

Daniel

Andy Cowan wrote:
>
> Try
>
>  
>
> Select * from companies where company_id not in (select distinct 
> company_id from todos) and company_id not in (select distinct...) etc.
>
>  
>
> Haven’t tried it, but it should work.
>
>  
>
> YMMV etc.
>
>  
>
> A.
>
>  
>
> Andy Cowan
>
> OpenSense Ltd
>
>  
>
>  
>
> *From:* phpwm-bounces at mailman.lug.org.uk 
> [mailto:phpwm-bounces at mailman.lug.org.uk] *On Behalf Of *Daniel Davies
> *Sent:* 03 April 2008 15:58
> *To:* West Midlands PHP User Group
> *Subject:* [Phpwm] SQL Problem
>
>  
>
> 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
>
>
> ------------------------------------------------------------------------
> Wave Rider Internet is a trading style of OpenSense Ltd.
> Registered in England and Wales No 04999653
> ------------------------------------------------------------------------
>
> _______________________________________________
> Phpwm mailing list
> Phpwm at mailman.lug.org.uk
> https://mailman.lug.org.uk/mailman/listinfo/phpwm
>   


-- 
Daniel Davies

Sitedesign.net Ltd
Birchley House
Birchfield Lane
Oldbury
West Midlands
B69 1DT

Tel: 0121 543 4840
Fax: 0121 543 4841

http://www.sitedesign.net

This email and its attachments may be confidential and are intended solely for the use of the individual to whom it is addressed. Any views or opinions expressed are solely those of the author and do not necessarily represent those of Sitedesign.net Ltd.