food grouping by table value parameters, etc.

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)

food grouping by table value parameters, etc.

Author

Topic

rjcom
Starting Member

3 Posts

Posted - 2013-01-12 : 04:57:24

Hi All,

I have three tables Food, Foodgroup, and FoodgroupCondition which I wish to take TVP then create first loop to use and iterate and pass FoodMatchString and FoodNotMatchString as variables (not written) and runs through the Food table and check those conditions and produces the fourth table Food_Foodgroup. Is this the right approach, I need help?

My current sql cursor doesn't have the FoodNotMatchString, but I haven't figured out it to get that to work.

----

DECLARE
@Food_FoodgroupID uniqueidentifier,
@FoodID int,
@FoodgroupID int

SET @Food_FoodgroupID = NewID()

DECLARE @FoodMatchString nvarchar(15)
// wish to set @FoodMatchString from table value parameter
SET @FoodMatchString = N'%beef%'
DECLARE db_cursor CURSOR FAST_FORWARD
FOR

SELECT Food.FoodID
FROM Food
WHERE Food.FoodName LIKE @FoodMatchString
OR Food.Ingredients LIKE @FoodMatchString

OPEN db_cursor
FETCH NEXT FROM db_cursor
INTO @FoodID

WHILE @@FETCH_STATUS = 0

BEGIN

// wish to set @FoodgroupID from table value parameter
SET @FoodgroupID = '5'

INSERT INTO dbo.Food_Foodgroup
SELECT NEWID(),
@FoodID,
@FoodgroupID
//only insert if not found
WHERE NOT EXISTS (

SELECT Food.FoodID, Food.FoodName, Food_Foodgroup.Food_FoodgroupID
FROM Food
INNER JOIN Food_Foodgroup
ON Food.FoodID = Food_Foodgroup.FoodID
WHERE Food_Foodgroup.FoodID = @FoodID
AND Food_Foodgroup.FoodgroupID =
@FoodgroupID
AND (Food.FoodName LIKE @FoodMatchString
OR Food.Ingredients LIKE @FoodMatchString)
)
FETCH NEXT FROM db_cursor
INTO @FoodID

END

CLOSE db_cursor
DEALLOCATE db_cursor

---

FoodgroupCondition table:
FoodgroupConditionID FoodMatchString FoodNotMatchString FoodgroupID
1 'beef' , 'beef fat' 1
2 'milk' , 'milky' 3
3 'carrot' , 'null' 2
4 'vegetable' , 'vegetable juice' 2
5 'soup' , 'null'' 4
6 'pie' , 'null' 5

Food table:
FoodID FoodName Ingredients
1 'beef curry', 'beef, milk'
2 'beef stew', 'beef, carrots'
3 'vegetable soup', 'vegetable'
4 pie crust' , 'beef fat'

Foodgroup table:
FoodgroupID FoodGroupName
1 meat
2 vegetable
3 dairy
4 soup
5 beef
6 baked goods

Food_Foodgroup table:
Food_FoodgroupID FoodID FoodgroupID
'342DAAB6-F95F-47AA-BA79-2B1E7064B019', 1, 1
'B021A8A4-37AB-4EDA-B7C3-333A194E470B', 1, 3
'95D72F10-F6CC-4C18-BC2F-58410E3AB340', 2, 1
'647E067F-DD5E-44E6-A407-8BBBFBC314E5', 2, 2
'6A3C320F-BC52-4A80-9373-9C1125D82857', 3, 2
'A46744B3-9983-48A4-892D-9EDEEC3267BD', 3, 4
'cfd05dc0-3b5a-4ed2-9bd5-9863a94923f4', 1, 5
'6d253e81-c6e2-4148-affe-05060cd450d9', 1, 5
'1b833946-f354-46a7-aeee-5e9d0a7219f0', 4, 6

I am doing this manually and wish to automate. Also, I don't like cursor or loops and see if it can written using a set based approach.

Any insight and help is much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-12 : 12:50:45

whats condition based on which you put ids? it seems to be matched from FoodName and Ingredients column alternatively. Is that correct?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rjcom
Starting Member

3 Posts

Posted - 2013-01-12 : 15:34:23

Thank you Visakhm, and sorry if I miss your point.

-- whats condition based on which you put ids?

The conditions based would be @FoodMatchString AND NOT @FoodNotMatchString first for the FoodName and Ingredients column then inserts a record to FoodCategory if it doesn't exist.

This would include the @FoodNotMatchString

SELECT Food.FoodID
FROM Food
WHERE Food.FoodName LIKE @FoodMatchString
OR Food.Ingredients LIKE @FoodMatchString
AND (NOT LIKE @FoodNotMatchString)

-- it seems to be matched from FoodName and Ingredients column alternatively. Is that correct?

Yes, it is doing that for now but I'm thinking to maybe checking FoodName, Ingredients, other column separately. I can't imagine of the loop structure though.

I still wish for TVP to be read and an iteration using set based approach, not cursors or loops. Is it possible, am I thinking wrong?

quote:
Originally posted by visakh16

whats condition based on which you put ids? it seems to be matched from FoodName and Ingredients column alternatively. Is that correct?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2013-01-12 : 17:30:43

If you really want help, please post the data in a readily consumable format using INSERT/SELECT statements to populate a table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".

First step towards the paradigm shift of writing Set Based code:
"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-13 : 23:02:01

quote:
Originally posted by rjcom

Thank you Visakhm, and sorry if I miss your point.

-- whats condition based on which you put ids?

The conditions based would be @FoodMatchString AND NOT @FoodNotMatchString first for the FoodName and Ingredients column then inserts a record to FoodCategory if it doesn't exist.

This would include the @FoodNotMatchString

SELECT Food.FoodID
FROM Food
WHERE Food.FoodName LIKE @FoodMatchString
OR Food.Ingredients LIKE @FoodMatchString
AND (NOT LIKE @FoodNotMatchString)

-- it seems to be matched from FoodName and Ingredients column alternatively. Is that correct?

Yes, it is doing that for now but I'm thinking to maybe checking FoodName, Ingredients, other column separately. I can't imagine of the loop structure though.

I still wish for TVP to be read and an iteration using set based approach, not cursors or loops. Is it possible, am I thinking wrong?

quote:
Originally posted by visakh16

whats condition based on which you put ids? it seems to be matched from FoodName and Ingredients column alternatively. Is that correct?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

do you mean you need to do comparison against group of columns to determine if row is to be considered?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rjcom
Starting Member

3 Posts

Posted - 2013-01-14 : 05:20:29

Yes, Visakhm you are correct. If a SQL script is ran correctly, then Food_Foodgroup will be created. I have posted the consumable data to better assist with this. Thank you.

DECLARE @Food TABLE ([FoodID] int NOT NULL,
[FoodName] varchar(30),
[Ingredients] text)

INSERT @Food
SELECT 1, N'beef curry', N'beef, milk' UNION ALL
SELECT 2, N'beef stew', N'beef, carrots' UNION ALL
SELECT 3, N'vegetable soup', N'vegetable' UNION ALL
SELECT 4, N'pie crust', N'beef fat'

SELECT * FROM @Food

DECLARE @Foodgroup TABLE (
[FoodgroupID] int NOT NULL,
[FoodgroupName] varchar(30))

INSERT @Foodgroup
SELECT 1, N'meat' UNION ALL
SELECT 2, N'vegetable' UNION ALL
SELECT 3, N'dairy' UNION ALL
SELECT 4, N'soup' UNION ALL
SELECT 5, N'beef' UNION ALL
SELECT 6, N'baked goods'

SELECT * FROM @Foodgroup

DECLARE @FoodgroupCondition TABLE (
[FoodgroupConditionID] int,
[FoodMatchString] varchar(20),
[FoodNotMatchString] varchar(20),
[FoodgroupID] int )

INSERT @FoodgroupCondition
SELECT 1, N'beef', N'beef fat', 1 UNION ALL
SELECT 2, N'milk', N'milky', 3 UNION ALL
SELECT 3, N'carrot', N'', 2 UNION ALL
SELECT 4, N'vegetable', N'vegetable juice', 2 UNION ALL
SELECT 5, N'soup', N'', 4 UNION ALL
SELECT 6, N'pie', N'', 5

SELECT * FROM @FoodgroupCondition

DECLARE @Food_Foodgroup TABLE (
[Food_FoodgroupID] uniqueidentifier,
[FoodID] int ,
[FoodgroupID] int )

INSERT @Food_Foodgroup
SELECT '342DAAB6-F95F-47AA-BA79-2B1E7064B019', 1, 1 UNION ALL
SELECT 'B021A8A4-37AB-4EDA-B7C3-333A194E470B', 1, 3 UNION ALL
SELECT '95D72F10-F6CC-4C18-BC2F-58410E3AB340', 2, 1 UNION ALL
SELECT '647E067F-DD5E-44E6-A407-8BBBFBC314E5', 2, 2 UNION ALL
SELECT '6A3C320F-BC52-4A80-9373-9C1125D82857', 3, 2 UNION ALL
SELECT 'A46744B3-9983-48A4-892D-9EDEEC3267BD', 3, 4 UNION ALL
SELECT 'cfd05dc0-3b5a-4ed2-9bd5-9863a94923f4', 1, 5 UNION ALL
SELECT '6d253e81-c6e2-4148-affe-05060cd450d9', 2, 5 UNION ALL
SELECT '1b833946-f354-46a7-aeee-5e9d0a7219f0', 4, 5

SELECT * FROM @Food_Foodgroup

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-14 : 05:48:47

even then there's an issue. The values are not 100 % matching
for example you've a value of vegetable soup in @Food whereas you've only value of soup in @Foodgroup table. So you cant just do a simple comparison to make them enter as record pair 3,4 in @Food_Foodgroup

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources