select *<br>from companies c<br>where not exists (select 1 from todos t where t.company_id = c.company_id)<br>and not exists (select 1 from activities a where a.company_id = c.company_id)<br><br><br>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.<br>
<br>Cheers,<br><br>Dave.<br><br><div class="gmail_quote">On Thu, Apr 3, 2008 at 3:57 PM, Daniel Davies <<a href="mailto:daniel@sitedesign.net">daniel@sitedesign.net</a>> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div bgcolor="#ffffff" text="#000000">
Hello all,<br>
<br>
Sorry, this is not strictly PHP, but its all the same kettle of fish.<br>
<br>
I'm working on a system that has companies, and these companies can
have activities and todos, defined something like:<br>
<br>
companies { <u>company_id</u>, name, etc... }<br>
todos { <u>todo_id</u>, <i>company_id</i>, etc... }<br>
activities { <u>activity_id</u>, <i>company_id</i>, etc... }<br>
<br>
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.<br>
<br>
Regards,<br>
<br>
Daniel<br>
<br>
</div>
<br>_______________________________________________<br>
Phpwm mailing list<br>
<a href="mailto:Phpwm@mailman.lug.org.uk">Phpwm@mailman.lug.org.uk</a><br>
<a href="https://mailman.lug.org.uk/mailman/listinfo/phpwm" target="_blank">https://mailman.lug.org.uk/mailman/listinfo/phpwm</a><br>
<br></blockquote></div><br>