RE: A graphic of the db relationship
> 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
RE: A graphic of the db relationship
> From: Michael T. Tangorre [mailto:[EMAIL PROTECTED] I forgot to change something when copy/pasting. 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_sub4 S4 ON (S3.id = S4.category_id) INNER JOIN auction_item_categories_sub5 S5 ON (S4.id = S5.category_id) WHERE A.id = 1 Sorry about that.. Mike ~| Flash for programmers - Flash MX Pro http://www.houseoffusion.com/banners/view.cfm?bannerid=56 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190640 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
Re: A graphic of the db relationship
Thanks Mike. I will consider your suggestions about putting all the data into one table. I did have a look at that database you mention and the layout was compelling. However, if the querying would still cause me headaches I would be no further ahead. Behind in fact, because I've had to redesign the application to fit to the new db structure. btw tried your query adn still no luck. Server Msg: -3100, State: 42000, [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '(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_sub4 S4 ON (S3.id = S4.category_id) INNER JOIN auc'. >> From: Michael T. Tangorre [mailto:[EMAIL PROTECTED] > >I forgot to change something when copy/pasting. > >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_sub4 S4 > ON (S3.id = S4.category_id) > INNER JOIN auction_item_categories_sub5 S5 > ON (S4.id = S5.category_id) >WHERE > A.id = 1 > >Sorry about that.. > >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:190698 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
RE: A graphic of the db relationship
Why don't you create a many -> many relationship if you want to use multiple categories? Table: auction_item Table: auction_category Table: item_category You only have to use 2 joins here, one join for getting category data, and one for getting category relations. SELECT DISTINCT AI.Label, AC.Label AS categoryLabel FROM Items AI JOIN ItemCategory IC ON (IC.ItemID = AI.ItemID) JOIN Categories AC ON (AC.CategoryID = IC.CategoryID) Micha Schopman Software Engineer Modern Media, Databankweg 12 M, 3821 AL Amersfoort Tel 033-4535377, Fax 033-4535388 KvK Amersfoort 39081679, Rabo 39.48.05.380 ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190703 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: A graphic of the db relationship
Try this.. FROM auction_item_categories AS A INNER JOIN auction_item_categories_sub AS S1 ON A.id = S1.category_id) INNER JOIN auction_item_categories_sub1 AS S2 ON S1.id = S2.category_id) INNER JOIN auction_item_categories_sub2 AS S3 ON S2.id = S3.category_id) INNER JOIN auction_item_categories_sub3 AS S4 ON S3.id = S4.category_id) INNER JOIN auction_item_categories_sub4 AS S5 ON S4.id = S5.category_id Also in your initial post the join was fine.. but you had the tables in FROM... and that was likly your cause.. did you try just using the Access Query tool to build you the query. but as it has already been pointed out.. it would be better to switch to one table. Protoculture wrote: > Thanks Mike. I will consider your suggestions about putting all the data into > one table. I did have a look at that database you mention and the layout was > compelling. > > However, if the querying would still cause me headaches I would be no further > ahead. Behind in fact, because I've had to redesign the application to fit to > the new db structure. > > btw tried your query adn still no luck. > > Server Msg: -3100, State: 42000, [Microsoft][ODBC Microsoft Access Driver] > Syntax error (missing operator) in query expression '(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_sub4 S4 > ON (S3.id = S4.category_id) > INNER JOIN auc'. > > > > >>>From: Michael T. Tangorre [mailto:[EMAIL PROTECTED] >> >>I forgot to change something when copy/pasting. >> >>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_sub4 S4 >> ON (S3.id = S4.category_id) >> INNER JOIN auction_item_categories_sub5 S5 >> ON (S4.id = S5.category_id) >>WHERE >> A.id = 1 >> >>Sorry about that.. >> >>Mike > > > ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190718 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
Re: A graphic of the db relationship
If you take Jochem's working query that gave no results you should consider changing the INNER JOINs to LEFT JOINs since this will find matching rows in the first table you list and then matching rows from the tables on the right of the LEFT JOIN. If there is no match you see a NULL for any values you are trying to get. Then you can concatenate the category names as you go using s1.name & "." & s2.name etc.. Protoculture wrote: >Thanks Mike. I will consider your suggestions about putting all the data into >one table. I did have a look at that database you mention and the layout was >compelling. > >However, if the querying would still cause me headaches I would be no further >ahead. Behind in fact, because I've had to redesign the application to fit to >the new db structure. > >btw tried your query adn still no luck. > >Server Msg: -3100, State: 42000, [Microsoft][ODBC Microsoft Access Driver] > Syntax error (missing operator) in query expression '(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_sub4 S4 >ON (S3.id = S4.category_id) >INNER JOIN auc'. > > > > > >>>From: Michael T. Tangorre [mailto:[EMAIL PROTECTED] >>> >>> >>I forgot to change something when copy/pasting. >> >>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_sub4 S4 >> ON (S3.id = S4.category_id) >> INNER JOIN auction_item_categories_sub5 S5 >> ON (S4.id = S5.category_id) >>WHERE >> A.id = 1 >> >>Sorry about that.. >> >>Mike >> >> > > ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190731 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
