[Phpwm] SQL Problem

[Phil Beynon]

Daniel,
The way below should work, but you may, depending on the overall structure have to do pre-queries to build the 'not in' lists.
I'm hoping whoever designed it actually put the different bits into different tables! :-)

Phil
  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



------------------------------------------------------------------------------


  No virus found in this incoming message.
  Checked by AVG. 
  Version: 7.5.519 / Virus Database: 269.22.5/1356 - Release Date: 02/04/2008 16:14
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.lug.org.uk/pipermail/phpwm/attachments/20080403/3d2c42ab/attachment-0001.html