| Author |
 Topic  |
|
|
rjcom
Starting Member
USA
3 Posts |
 Posted - 01/12/2013 : 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.
|
Edited by - rjcom on 01/12/2013 06:34:58
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 01/12/2013 : 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
USA
3 Posts |
Posted - 01/12/2013 : 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
USA
643 Posts |
Posted - 01/12/2013 : 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
India
47040 Posts |
Posted - 01/13/2013 : 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
USA
3 Posts |
Posted - 01/14/2013 : 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
India
47040 Posts |
Posted - 01/14/2013 : 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/
|
|
|
| |
Topic |
|
|
|
food grouping by table value parameters, etc.
