I'm not sure why you are using parenthesis for your joins since SQL
doesn't need them. This may sound horribly simple but I noticed a
change in your field naming convention throughout this thread. You have
category_id for all your tables except for your alias s4
(auction_item_categories_sub4) where you have a field with an extra 'y'
categoryy_id - could this be just a simple typo or was the deviation
deliberate?
Also, as a note on form,
1. do you really need SELECT * ? - enumerating the fields is always
preferred over SELECT *
2. While Access doesn't support stored procs, you should consider using
cfqueryparam instead of the hardcoded value to get the benefit of
turning this call into a prepared statement, especially with this many
joins involved. Subsequent calls can then take advantage of a cached
execution plan.
Try:
SELECT *
FROM auction_item_categories_sub5 s5
INNER JOIN auction_item_categories_sub4 s4
ON s5.category_id = s4.id
INNER JOIN auction_item_categories_sub3 s3
ON s4.categoryy_id = s3.id
INNER JOIN auction_item_categories_sub2 s2
ON s3.category_id = s2.id
INNER JOIN auction_item_categories_sub s
ON s2.category_id = s.id
INNER JOIN auction_item_categories c
ON s.category_id = c.id
WHERE auction_item_categories.id = 1
Regards,
Adam Howitt
Protoculture wrote:
>Jochem. tried your code, but got the following error.
>
>Server Msg: -3010, State: 07002, [Microsoft][ODBC Microsoft Access Driver]
> Too few parameters. Expected 2.
>
>with this code you supplied.
>
>SELECT *
>
>FROM (((( auction_item_categories_sub5 s5
> INNER JOIN auction_item_categories_sub4 s4 ON s5.category_id =
> s4.id)
> INNER JOIN auction_item_categories_sub3 s3 ON s4.categoryy_id =
> s3.id)
> INNER JOIN auction_item_categories_sub2 s2 ON s3.category_id =
> s2.id)
> INNER JOIN auction_item_categories_sub s ON s2.category_id = s.id)
> INNER JOIN auction_item_categories c ON s.category_id = c.id
>
>WHERE auction_item_categories.id = 1
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190622
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
