> From: Protoculture [mailto:[EMAIL PROTECTED]
> The Last Code I tried was Jochems and while it did run
> succesfully, there were no results returned. Essentially What
> I'm doing is selecting all related categories from the where
> all the ( auction_item_categories_sub.category_id = 1 ) and
> then it should filter down with the appropriate logic. The
> trouble is, I'm not having a great time finding that logic.
I would recommend rethinking the DB structure. The fact that you have the
category hierarchy broken out into individual tables is not a good approach
in my opinion. It looks like you could benefit more from a "set" or "tree"
structure that would allow you to store the entire hierarchy in one table.
Google "Joe Celko Nested Set" and check it out. The code is out that for
selecting hierarchies, inserting a new "node" and removing "nodes".
Given your current setup, remember thah the inner join will take care of
only filtering out records where there is no ID match (PK/FK).
Start with the top level...
SELECT
A.name
FROM
auction_item_categories A
INNER JOIN auction_item_categories_sub S1 ON (A.id = S1.category_id)
INNER JOIN auction_item_categories_sub2 S2 ON (S1.id =
S2.category_id)
INNER JOIN auction_item_categories_sub3 S3 ON (S2.id =
S3.cateogyr_id)
INNER JOIN auction_item_categories_sub2 S4 ON (S3.id =
S4.category_id)
INNER JOIN auction_item_categories_sub2 S5 ON (S4.id =
S5.category_id)
WHERE
A.id = 1
Give the above a try...
Mike
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190639
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
