IMHO proper normalization would be to assign a CategoryID to the
SubCategory table if that works in your business rules. But the way the
schema is now you can just write a joined query like:
SELECT *
FROM Category C, SubCategory SC, Product P
WHERE C.CategoryID = P.CategoryID
AND SC.CategoryID = P.SubCategoryID
AND P.ProductID = #attributes.productid#
Greg Luce
954-763-4504
-----Original Message-----
From: Chad Gray [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 01, 2001 10:36 AM
To: CF-Talk
Subject: SQL question about related tables
Im wondering what is the best way to get related data from two
tables. This would be the MySQL database structure:
Table Name: Category
CategoryID (autonumber)
CategoryName (text)
Table Name: Subcategory
SubcategoryID (autonumber)
SubcategoryName (text)
Table Name: Product
ProductID (autonumber)
CategoryID (linked to category table)
SubcategoryID (linked to subcategory table)
ProductSKU (text)
If i know the ProductID, what is the BEST way to get the CategoryName,
and
SubcategoryName?
Two Querys (one in a CFLOOP) ?
Query inside of a Query ?
INNER JOIN ?
WHERE Product.CategoryID = Category.CategoryID ?
Thanks In advanced,
Chad
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
