Many thanks - I was focusing on tblGroups and didn't even think going the
other way as you have suggested
your first code worked beautifully
thanks again
Seamus
At 11:59 am 10/05/01 , you wrote:
>You could probably do this with cfloop but why. Let the database do the
>work for you. I am a little confused to when you say zero product
>descriptions. Does this mean an entry in tblproduct where there is a
>groupcode but no productdescription, meaning its null. Another option,
>which what I think you mean, is that there is an entry in tblgroup but no
>entries in tblproduct with that groupcode.
>
>I can see a couple of ways to get what you want through sql.
>
>Select groupcode from tblGroup where groupcode in (Select distinct groupcode
>from tblproduct)
>
>or
>
>Select distinct tblGroup.GroupCode from tblGroup left outer join tblProduct
>on tblGroup.GroupCode = tblProduct.GroupCode
>
>Hope this helps.
>
>John Hancock
>
>-----Original Message-----
>From: Seamus Campbell [mailto:[EMAIL PROTECTED]]
>Sent: Wednesday, May 09, 2001 8:29 PM
>To: CF-Talk
>Subject: cfloop question (I think)
>
>
>I have a database with (amongst other things) a table:
>
>tblProduct
>ProductDescription and GroupCode
>
>another table:
>
>tblGroup
>GroupCode and GroupDescription
>
>The database is updated regularly
>there are 31 GroupCodes but sometimes (and this varies) one or more
>GroupCodes have zero ProductDescriptions in them
>
>I want to be able to have a list of GroupCodes
>but only show the GroupCodes which actually have records
>
>I'm sure there must be a way to do this with cfloop but
>I can't see how to do it
>
>Can anyone help please
>Seamus
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists