What do you mean by "output a single row"?
You mean sth like
FoodName MoreFoodInfo Nutrient1 Value Nutrient2 Value.....
-------- ------------ --------- ----- --------- -----
Name more stuff ENERGY-SOA 191.574 PROT FAT
where the number of columns is controlled by the number of rows
in NIP_Nutrient for that food?
If so, you can't do it in one SQL statement.
Presumably you are doing this in CF though,
in which case you can just join the tables:
SELECT F.col1, F.col2.... N.value, N.nutrient
FROM NIP_FOOD F, NIP_Nutrient N
WHERE F.FOOD_ID = N.FOOD_ID
and then use CFML to build up the display row by looping
through the 7 dataset rows returned.
Nick
-----Original Message-----
From: Tilbrook, Peter [mailto:[EMAIL PROTECTED]]
Sent: Friday, August 17, 2001 1:38 AM
To: CF-Talk
Subject: Database query question
I've been given a massive (130Mb) Access database with two tables.
NIP_FOOD and NIP_Nutrient.
NIP_FOOD contains a unique ID field which is Alphanumeric (eg:
01A10014).
NIP_Nutrient contains a field called "FOOD_ID" which is seven seperate
rows of the same value as ID (but not linked). eg:
FOOD_ID VALUE NUTRIENT
01A10014 191.574 ENERGY-SOA
01A10014 0.4 PROT
01A10014 0 FAT
01A10014 0 TOTSATFD
01A10014 2.492 CHO-SOA
01A10014 0 TOTSUG
01A10014 10 NA-SOA
What would be the way to retrieve all of the fields from both tables
using the value (eg: 01A10014) but have it output a single row?
It will stay in Access until I can get IT to port it to SQL Server or
Oracle.
Thanks!
Peter Tilbrook
ColdFusion Applications Developer
Australia New Zealand Food Authority
Boeing House
55 Blackall Street
BARTON ACT 2600
Ph: +61-2-6271 2256
Fax: +61-2-6271 2278
http://www.anzfa.gov.au
**********************************************************************
This transmission is intended only for the use of the addressee(s)
and may contain confidential or legally privileged information. If you are
not the intended recipient, you are notified that any use or dissemination
of this communication is strictly prohibited. If you have received this
transmission in error, please notify the ANZFA IT helpdesk prior to deleting
all copies of this transmission together with any attachments.
ANZFA helpdesk:
E-mail: [EMAIL PROTECTED]
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**********************************************************************
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
