SQL Server - Select Query problem

Asked By shekhar kumar on 25-Nov-09 06:28 AM
I have three table. 
Links   - Link_Key, Link_Name, Link_NewWindow, Link_URL, Link_Order
Item -   Item_Key, Item_LinkKey, ...
SubItem - SubItem_Key, SubItem_ItemKey,....

Now I am writing a query as below

select Links.*,
count(Item_key) as Total_Item, 
count(SubItem_Key) as Total_SubItem, 
Item_k=case count(item_key)
when 1 then Item_key
else 0
end
from links 
left outer join Item on Link_key=Item_LinkKey  
left outer join SubItem on Item_Key=SubItem_ItemKey
where link_order>0  
group by Link_Key,Link_Name,Link_NewWindow,Link_URl,Link_Order, Item_key
order by Link_Order

It is running good but I want to select Item_k only when it is one corresponding to each Link_Key

is it possible to do so.

Thanks
Shekhar. 
Jonathan VH replied to shekhar kumar on 25-Nov-09 06:56 AM
I don't think your query would give you a meaningful result for Total_Item if any items have multiple SubItems, so I changed that for you, too:
 
SELECT k.Link_Key, k.Link_Name, k.Link_NewWindow, k.Link_URl, k.Link_Order,
COUNT(DISTINCT i.Item_Key) AS Total_Item, COUNT(s.SubItem_Key) AS Total_SubItem, 
CASE WHEN COUNT(DISTINCT i.Item_Key) = 1 THEN MAX(i.Item_Key) ELSE 0 END AS Item_Key
FROM dbo.Links k
LEFT OUTER JOIN dbo.Item i ON k.Link_Key = i.Item_LinkKey
LEFT OUTER JOIN dbo.SubItem s ON i.Item_Key = s.SubItem_ItemKey
GROUP BY k.Link_Key, k.Link_Name, k.Link_NewWindow, k.Link_URl, k.Link_Order
ORDER BY k.Link_Order;

Thanks a lot

shekhar kumar replied to Jonathan VH on 25-Nov-09 07:04 AM
Thanks a lot. You were right when there was multiple subitems for each item the value of total item and subitems was same.

Thanks a lot again.