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
