You cannot retrieve multiple records in a sub-query, only one. You want
SELECT DISTINCT A.itemID, B.CategoryID FROM items A INNER JOIN Categories B ON A.ItemID = B.itemID WHERE A.itemID = <whatever> If you only want category ID's and nothing else, try: SELECT DISTINCT categoryID FROM categories Then just loop through and only output the categoryID field. -----Original Message----- From: DKI [mailto:[EMAIL PROTECTED] Sent: Thursday, May 10, 2007 3:20 PM To: CF-Talk Subject: Db query help needed Hi, I have a sql query like this that works fine: SELECT itemID, (SELECT COUNT (DISTINCT categoryid) FROM categories WHERE itemidID = items.itemID) AS categorycount FROM items But what i need is not the count, but the actual list of categories, as one column, in a single subquery. I tried the query below and obviously got an error message. But still i am giving it you to show what i need. Any ideas guys? Db used: ms sql 2000. Please help. Here's what i am using: SELECT itemID, (SELECT categoryid FROM categories WHERE itemidID = items.itemID) AS categoryidList FROM items Thanks, K ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:277664 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
