Is There a better way - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Is There a better way

Author

Topic

pwarnk
Starting Member

4 Posts

Posted - 2011-02-07 : 09:46:36

OK SQL Gods is there a better way?

SELECT recipeId, UserCount, KitchenId, RecipeCount
FROM (SELECT userTable.recipeId, userTable.UserCount, userTable.KitchenId, COUNT(DISTINCT Ingredients_1.foodItemId) AS RecipeCount
FROM (SELECT TOP (100) PERCENT Ingredients.recipeId, COUNT(DISTINCT userFoodItems.foodItemId) AS UserCount, userFoodItems.KitchenId
FROM Ingredients LEFT OUTER JOIN
userFoodItems ON Ingredients.foodItemId = userFoodItems.foodItemId
GROUP BY Ingredients.recipeId, userFoodItems.KitchenId
HAVING (Ingredients.recipeId IS NOT NULL)
ORDER BY Ingredients.recipeId) AS userTable INNER JOIN
Ingredients AS Ingredients_1 ON userTable.recipeId = Ingredients_1.recipeId
GROUP BY userTable.recipeId, userTable.UserCount, userTable.KitchenId) AS CanMake
WHERE (UserCount = RecipeCount)

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-02-07 : 09:50:51

OK SQL Gods is there a better way?

to cook rice?
to work faster?
to get the right result?

you haven't told us your objective

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-07 : 09:59:16

No untill you come up with some more details

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-07 : 10:27:59

Here's at least some formatting for what you wrote:


SELECT
	recipeId
	, UserCount
	, KitchenId
	, RecipeCount
FROM
	(
	SELECT
		userTable.recipeId
		, userTable.UserCount
		, userTable.KitchenId
		, COUNT(DISTINCT Ingredients_1.foodItemId) AS RecipeCount
	FROM (
		SELECT TOP (100) PERCENT
			Ingredients.recipeId
			, COUNT(DISTINCT userFoodItems.foodItemId) AS UserCount
			, userFoodItems.KitchenId
		FROM
			Ingredients
			LEFT OUTER JOIN userFoodItems ON Ingredients.foodItemId = userFoodItems.foodItemId
		GROUP BY
			Ingredients.recipeId
			, userFoodItems.KitchenId
		HAVING (Ingredients.recipeId IS NOT NULL)
		ORDER BY
			Ingredients.recipeId
		)
		AS userTable

		INNER JOIN Ingredients AS Ingredients_1 ON userTable.recipeId = Ingredients_1.recipeId
	GROUP BY
		userTable.recipeId
		, userTable.UserCount
		, userTable.KitchenId
	)
	AS CanMake
WHERE
	(UserCount = RecipeCount)

I don't know what you are trying to do but I will stake my reputation as a 'SQL God' that there is a better way to do it!

For example this part:


	FROM (
		SELECT TOP (100) PERCENT
			Ingredients.recipeId
			, COUNT(DISTINCT userFoodItems.foodItemId) AS UserCount
			, userFoodItems.KitchenId
		FROM
			Ingredients
			LEFT OUTER JOIN userFoodItems ON Ingredients.foodItemId = userFoodItems.foodItemId
		GROUP BY
			Ingredients.recipeId
			, userFoodItems.KitchenId
		HAVING (Ingredients.recipeId IS NOT NULL)
		ORDER BY
			Ingredients.recipeId
		)
		AS userTable

Is pretty damn suspect. TOP 100 PERCENT -- that's all the rows right? Also the table [Ingredients] forms the left side of your LEFT JOIN but you are expressing a HAVING clause that states [Ingredients].[recipeID] IS NOT NULL -- that could drop down into the WHERE clause so you don't need to join over any rows where that is the case....

I see only 2 tables are used here [Ingredients] and [userFoodItems]

Can you give us:
1) An English description of what you actually want
2) the structure for each table
3) some sample data for each table
4) The required results for that data.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

pwarnk
Starting Member

4 Posts

Posted - 2011-02-07 : 11:05:50

Objective there are two tables one that are (A) ingredients to recipes; (B) on that are food items a user has in the kitchen.
I need to extract all the recipes where the user has all the food items to make the recipe.

USE [recipes]
GO

/****** Object: Table [dbo].[Ingredients] Script Date: 02/07/2011 11:02:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Ingredients](
[IngId] [int] IDENTITY(1,1) NOT NULL,
[amt] [varchar](50) NOT NULL,
[units] [varchar](50) NULL,
[modifier] [varchar](1000) NULL,
[item] [varchar](1000) NOT NULL,
[prep] [varchar](1000) NULL,
[notes] [varchar](1000) NULL,
[recipeId] [int] NULL,
[foodItemId] [int] NOT NULL,
CONSTRAINT [PK_Ingredients] PRIMARY KEY CLUSTERED
(
[IngId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Ingredients] WITH CHECK ADD CONSTRAINT [FK_Ingredients_foodItems] FOREIGN KEY([foodItemId])
REFERENCES [dbo].[foodItems] ([itemId])
GO

ALTER TABLE [dbo].[Ingredients] CHECK CONSTRAINT [FK_Ingredients_foodItems]
GO

ALTER TABLE [dbo].[Ingredients] WITH CHECK ADD CONSTRAINT [FK_Ingredients_recipes] FOREIGN KEY([recipeId])
REFERENCES [dbo].[recipes] ([rowID])
GO

ALTER TABLE [dbo].[Ingredients] CHECK CONSTRAINT [FK_Ingredients_recipes]
GO
-*-*-*-*-*-*-*-*--*
USE [recipes]
GO

/****** Object: Table [dbo].[userFoodItems] Script Date: 02/07/2011 11:03:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[userFoodItems](
[userFoodItemKey] [int] IDENTITY(1,1) NOT NULL,
[KitchenId] [int] NOT NULL,
[locationId] [int] NOT NULL,
[foodItemId] [int] NOT NULL,
[expires] [date] NULL,
CONSTRAINT [PK_userFoodItems] PRIMARY KEY CLUSTERED
(
[userFoodItemKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[userFoodItems] WITH CHECK ADD CONSTRAINT [FK_userFoodItems_foodItems] FOREIGN KEY([foodItemId])
REFERENCES [dbo].[foodItems] ([itemId])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[userFoodItems] CHECK CONSTRAINT [FK_userFoodItems_foodItems]
GO

ALTER TABLE [dbo].[userFoodItems] WITH CHECK ADD CONSTRAINT [FK_userFoodItems_Kitchens] FOREIGN KEY([KitchenId])
REFERENCES [dbo].[Kitchens] ([KitchenID])
GO

ALTER TABLE [dbo].[userFoodItems] CHECK CONSTRAINT [FK_userFoodItems_Kitchens]
GO

ALTER TABLE [dbo].[userFoodItems] WITH CHECK ADD CONSTRAINT [FK_userFoodItems_userLocations] FOREIGN KEY([locationId])
REFERENCES [dbo].[userLocations] ([locationId])
GO

ALTER TABLE [dbo].[userFoodItems] CHECK CONSTRAINT [FK_userFoodItems_userLocations]
GO

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-07 : 11:30:22

OK So lets see if I follow you. I think your database design is a quite denormalised.

You have a table called Ingredients. This holds Ingredients (carrots, peas, steak, etc)?

Why does an ingredient have a reference to a recipe? ([recipeID] which references a table called [recipies])

Does this mean that you may have multiple entries for "Carrot" in your ingredients table each with a foreign key to a recipe they are part of?

Doesn't that sound exactly the wrong way round?

In a typical normalised database I'd expect the following rough structure:

Table : Ingredients (holds 1 row per ingredient type (carrots would only appear once in this table for example)
Table : Recipies (holds 1 row per recipe name (Green Thai Curry, Toasted Cheese, whatever)
Table : RecipieIngredients (a Many to Many table which in it's simplest form could have only two fields [recipieID], [IngredientID]) but would probably also hold fields for [QuantityRequired], etc

Then to find out which ingredients you need for any particular recipe you would issue:


SELECT
	r.[name]
	, i.[name]
FROM
	recipe AS r
	JOIN recipeIngredients AS ri ON ri.[recipieID] = r.[recipeID]
	JOIN ingredient AS i ON i.[ingredientID] = ri.[ingredientID]
WHERE
	r.[recipeName] = 'Cheese on Toast'

Does that make any sense?

Can you provide some sample data for your tables?

Also -- the XML plan isn't any use, please delete it - I think other might get turned off by that.

If you can model this in a normalised way you'll find that it should be really easy to answer questions like : get me all the possible recipes a particular person can make.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

pwarnk
Starting Member

4 Posts

Posted - 2011-02-07 : 12:47:39

Here is the problem RAI for Silverlight does not handle Many To Many well so can use it.

So

Recipes (One Many) Ingredients (One One) FoodItem (One One) UserFoodItems

Table Recipe
ID: 1
Title: Beef Stew
Servers: 8

Table Ingredients
IngID: 1
recipeID: 1
Description: Cleaned Carrots
foodID: 1
IngID: 2
recipeID: 1
Description: Beef Roast Trimed and Cleaned
foodID: 2
IngID: 3
recipeID: 1
Description: Pealed Potatoes
foodID: 3

Table FoodItems
FoodID: 1
Value: Carrots
FoodID: 2
Value: Beef Roast
FoodID: 3
Value: Potatoes

UserFoodItems
FoodID:1
KitchenID:1
FoodID:2
KitchenID:1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources