SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 food grouping by table value parameters, etc.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rjcom
Starting Member

USA
3 Posts

Posted - 01/12/2013 :  04:57:24  Show Profile  Reply with Quote
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.

Edited by - rjcom on 01/12/2013 06:34:58

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/12/2013 :  12:50:45  Show Profile  Reply with Quote
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/

Go to Top of Page

rjcom
Starting Member

USA
3 Posts

Posted - 01/12/2013 :  15:34:23  Show Profile  Reply with Quote

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/



Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/12/2013 :  17:30:43  Show Profile  Reply with Quote
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."
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/13/2013 :  23:02:01  Show Profile  Reply with Quote
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/

Go to Top of Page

rjcom
Starting Member

USA
3 Posts

Posted - 01/14/2013 :  05:20:29  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/14/2013 :  05:48:47  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000