Hi Peter
Something like this should do the trick
<cfquery name="select" datasource="testdb" dbtype="ODBC">
select f.LONG_NAME, n.VALUE, n.NUTRIENT
from NIP_FOOD f, NIP_Nutrient n
where f.id = '01A10014'
</cfquery>
Then just output the query as normal (without the aliases)
HTH
Mark
Mark Smyth
Macromedia Certified ColdFusion Developer
Systems Union eBusiness Solutions
01865 880800
[EMAIL PROTECTED]
www.systemsunion.net
-----Original Message-----
From: Tilbrook, Peter [mailto:[EMAIL PROTECTED]]
Sent: 20 August 2001 05:35
To: CF-Talk
Subject: Db 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) and NOT indexed, eg:
ID LONG_NAME
01A10014 Beer, Ale
01A10015 Beer, Bitter Or Draught
01A10016 Beer, Regular Alcohol, Home Brewed
01A10017 Beer, Lager
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 ID value (eg: 01A10014) but have it output a single row, eg:
ID LONG_NAME ENERGY-SOA PROT FAT TOTSATFD
CHO-SOA TOTSUG NA-SOA
01A10014 Beer, Ale 191 0.4 0 0
2.492 0 10
01A10015 Beer, Bitter 191.622 0.3 0 0
2.726 0.2 7
etc
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
