• 0

[SQL] Get TOP 5 based on secondary query


Question

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

  • 0

Sorry, I should have stated that. I am not relating via customer, but by item id:

Restaurants.dbo.ITEMS.ITM_UID to CUSTOMERS.dbo.ORDER_HAS_ITEMS.OHI_ITM_UID

Thanks,

James

Link to comment
Share on other sites

  • 0

Items should have their own table surely for normalization purposes and rather than reference two databases why not have these all in one database?

Rather than connect the two databases together I would just create a view that pulled the data from the database, as this won't need to be manipulated.

Link to comment
Share on other sites

  • 0

I agree that the multiple databases (there are actually 3) was a bad design, and there are reasons to have seperate database, just not this design. (but I really don't want to get into the designs of this system as it'll make me feel ill)

I will think about the view as an option. Thanks.

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.