> I have two tables: Articles, ArticleCat(egorie)s. At some point I want to
> list all categories and allow checkboxes for multiple deletes,
> but only when
> a category is empty (no articles).
>
> I want to loop through a query of ArticleCats and test if there
> are articles
> under that category. The only thing I can think of is to run query each
> iteration of the loop, which does not sound right.:
>
> <cfquery name="getarticleCats" dsn=dsn>
> Select ID, Name
> From ArticleCats
> </cfquery>
>
> <cfloop query="getArticleCats">
> <cfquery name="isAssigned" maxrows=1>
> Select ID from Articles
> Where A_Cat=#getArticleCats.ID#
> </cfquery>
> <cfif NOT isAssigned(RecordCount)=0>
> <cfset deleteOK=False>
> </cfif>
>
> This sounds like a huge burden. Is there another way to do it?
If they're in the same datasource (you didn't supply one for the second
query) then you can just use an OUTER JOIN
select ac.ID, ac.Name
from ArticleCats as ac left outer join Articles as a on a.A_Cat=ac.ID
where a.A_Cat is NULL
Please note that I haven't tested this, it's written off the top of my head,
so may need some playing with - but, saying that, it should give you exactly
what you want
That is assuming that you only want the ones where there are no entries in
Articles table
If you want it to show when there are entries, then try;
select distinct ac.ID, ac.Name
from ArticleCats as ac join Articles as a on a.A_Cat=ac.ID
I have put the DISTINCT in there, otherwise it'll produce one record for
every entry in the Articles table (which could ammount to a lot)
HTH (and works <g>)
Philip Arnold
ASP Multimedia Limited
T: +44 (0)20 8680 1133
"Websites for the real world"
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**********************************************************************
------------------------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message
with 'unsubscribe' in the body to [EMAIL PROTECTED]
