Hey gang, I feel embarased as after all these years I should be able to create this query... but today my brain is mush. (so, feel free to berate me with the answer)
I need to create a collection the top 5 items based on two seperate databases
In db1 (Customers) we have items (food, if you care) that have been ordered and I need to show the top 5 items based on the users location (ZIP Code)
The first query is as follows: (yes, it's not quite there. but it's close) This gives me the items, the details and the cost
(from db "Restaurants")
SELECT TOP 5
ITEMS.ITM_UID,
ITEMS.ITM_NAME,
ITEMS.ITM_DESCRIPTION,
ITEMS.ITM_PRICE,
RESTAURANTS.RST_NAME
FROM ITEMS
INNER JOIN CATEGORIES ON ITEMS.ITM_CAT_UID = CATEGORIES.CAT_UID
INNER JOIN MENUS ON CATEGORIES.CAT_MNU_UID = MENUS.MNU_UID
INNER JOIN RESTAURANTS ON MENUS.MNU_RST_UID = RESTAURANTS.RST_UID
Question
James Rose
Hey gang, I feel embarased as after all these years I should be able to create this query... but today my brain is mush. (so, feel free to berate me with the answer)
I need to create a collection the top 5 items based on two seperate databases
In db1 (Customers) we have items (food, if you care) that have been ordered and I need to show the top 5 items based on the users location (ZIP Code)
The first query is as follows: (yes, it's not quite there. but it's close) This gives me the items, the details and the cost
(from db "Restaurants")
SELECT TOP 5
ITEMS.ITM_UID,
ITEMS.ITM_NAME,
ITEMS.ITM_DESCRIPTION,
ITEMS.ITM_PRICE,
RESTAURANTS.RST_NAME
FROM ITEMS
INNER JOIN CATEGORIES ON ITEMS.ITM_CAT_UID = CATEGORIES.CAT_UID
INNER JOIN MENUS ON CATEGORIES.CAT_MNU_UID = MENUS.MNU_UID
INNER JOIN RESTAURANTS ON MENUS.MNU_RST_UID = RESTAURANTS.RST_UID
WHERE RESTAURANTS.RST_ZIP = '10016'
ORDER BY ITEMS.ITM_NAME
The second query gives me the top 5
(from db "Customers")
SELECT TOP 5
COUNT(OHI_ITM_UID) AS ITEMCOUNT,
OHI_ITM_UID
FROM CUSTOMERS.dbo.ORDER_HAS_ITEMS
GROUP BY OHI_ITM_UID
ORDER BY ITEMCOUNT DESC
So, how do I connect?
Thanks.
Link to comment
Share on other sites
4 answers to this question
Recommended Posts