May 31, 2011 at 3:24 pm
I have three tables
A Table listing Animals (Names)
A Table listing Food (Names)
A Table linking the Animal to the Food it likes (a many to many relationship)
I need a sproc that for a given food ID,
...return a Sigle String (concat'd comma separated)
...of the Animals (names) that currently like that food.
...The names must be in alphabetical order.
...No Cursors allowed.
Here is the schema and test data:
CREATE TABLE dbo.Animal(
AnimalID int NOT NULL,
AnimalName varchar(50) NOT NULL,
Deleted bit NOT NULL,
CONSTRAINT [PK_Animal] PRIMARY KEY CLUSTERED
(
AnimalID 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
INSERT dbo.Animal SELECT 1, 'Spot', 0
INSERT dbo.Animal SELECT 2, 'Fido', 0
INSERT dbo.Animal SELECT 3, 'Woffer', 1 -- Woffer has gone to the farm
INSERT dbo.Animal SELECT 4, 'King', 0
INSERT dbo.Animal SELECT 5, 'Smokey', 0
CREATE TABLE dbo.Food(
FoodID int NOT NULL,
FoodName varchar(20) NOT NULL,
Deleted bit NOT NULL,
CONSTRAINT [PK_Food] PRIMARY KEY CLUSTERED
(
FoodID 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
INSERT Food SELECT 1, 'Pork', 0
INSERT Food SELECT 2, 'Chicken', 0
INSERT Food SELECT 3, 'Duck', 1 -- All the Ducks have flown South
INSERT Food SELECT 4, 'Beef', 0
INSERT Food SELECT 5, 'Turkey', 0
CREATE TABLE dbo.AnimalFood(
AnimalID int NOT NULL,
FoodID int NOT NULL,
Deleted bit NOT NULL,
CONSTRAINT [PK_AnimalFood] PRIMARY KEY CLUSTERED
(
AnimalID ASC,
FoodID 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].[AnimalFood] WITH CHECK ADD CONSTRAINT [FK_AnimalFood_Animal] FOREIGN KEY([AnimalID])
REFERENCES [dbo].[Animal] ([AnimalID])
GO
ALTER TABLE [dbo].[AnimalFood] CHECK CONSTRAINT [FK_AnimalFood_Animal]
GO
ALTER TABLE [dbo].[AnimalFood] WITH CHECK ADD CONSTRAINT [FK_AnimalFood_Food] FOREIGN KEY([FoodID])
REFERENCES [dbo].[Food] ([FoodID])
GO
ALTER TABLE [dbo].[AnimalFood] CHECK CONSTRAINT [FK_AnimalFood_Food]
GO
INSERT AnimalFood SELECT 1, 2, 0 -- Spot likes Chicken
INSERT AnimalFood SELECT 1, 3, 0 -- Spot likes Duck (when in season)
INSERT AnimalFood SELECT 1, 5, 0 -- Spot likes Turkey
INSERT AnimalFood SELECT 2, 1, 0 -- Fido likes Pork
INSERT AnimalFood SELECT 2, 2, 1 -- Fido used to like Chicken but does not any more
INSERT AnimalFood SELECT 2, 4, 0 -- Fido likes Beef
INSERT AnimalFood SELECT 3, 1, 0 -- Woffer liked Pork
INSERT AnimalFood SELECT 3, 2, 0 -- Woffer liked Chicken
INSERT AnimalFood SELECT 3, 4, 0 -- Woffer liked Beef
INSERT AnimalFood SELECT 3, 5, 0 -- Woffer liked Turkey
INSERT AnimalFood SELECT 4, 2, 1 -- King used to like Chicken but now doesn't like anything
INSERT AnimalFood SELECT 5, 2, 0 -- Smokey likes Chicken
INSERT AnimalFood SELECT 5, 4, 0 -- Smokey likes Beef
INSERT AnimalFood SELECT 5, 5, 1 -- Smokey used to like Turkey
Expected results:
1 returns 'Fido' (Woffer is excluded because deleted=1)
2 returns 'Smokey, Spot' (not 'Fido, Smokey, Spot' edited my mistake)
3 returns 'Spot'
4 returns 'Fido, Smokey'
5 returns 'Spot' (not 'Smokey, Spot' - another mistake)
May 31, 2011 at 3:56 pm
I'm not sure I'm following your expected results.
According to the data, type 2 (chicken), was liked by 5 animals, but 3 are filtered out:
Woofer is deleted in Animal table.
Fido and King are are deleted in AnimalFood for food type 2.
So you expect Fido, Smokey, and Spot, yet only Smokey and Spot like chicken.
Also, you say 3 should return Spot, yet 3 has been deleted from Food. Do you still need to see these?
Finally, for FoodID 5, you are expecting Smokey and Spot. However, smokey has Deleted = 1 from AnimalFood. Why are we expecting him in the result set?
As for the technical solution, if cursors are out, will While Loops work?
For now, how about this?
DECLARE @foodid TINYINT
SET @foodid = 2
DROP TABLE #temp
SELECT
af.animalid, a.animalname, a.DELETED 'animal_del', af.foodid, f.foodname, f.DELETED 'food_del', af.DELETED 'af_deleted'
INTO #temp
FROM animalfood af
JOIN animal a ON a.animalid = af.animalid
JOIN food f
ON f.foodid = af.foodid
WHERE af.foodid = @foodid
AND a.DELETED = 0
AND f.DELETED = 0
AND af.DELETED = 0
DECLARE @maxrows INT, @rows int
SELECT @maxrows = @@rowcount, @rows = 1
WHILE 1=1
BEGIN
DECLARE @int INT, @name VARCHAR(MAX), @namestring VARCHAR(max)
SELECT TOP 1 @int = animalid,
@name = animalname
FROM #temp
ORDER BY animalname
IF @rows = 1
SELECT @namestring = @name
ELSE
SELECT @namestring = @namestring + ',' + @name
SET @rows = @rows + 1
DELETE FROM #temp WHERE animalid = @int
IF @rows > @maxrows
BREAK
END
SELECT @namestring
May 31, 2011 at 5:19 pm
Hi Thomas.
Nice setup. Thanks.
If you write the code to return the food ID and the list of animals that like the food (food ID and animal name make a unique row, I'll show you how to concatenate it without any form of RBAR at all.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 1, 2011 at 7:32 am
Thanks Mister Sachmo (& I did make a mistake on the Chicken & Turkey examples).
June 1, 2011 at 7:45 am
Mister Sachmo,
Also, yes I wanted to allow for #3 Duck, because it might come back in season.
The while works, but I am interested in something with the best performance.
Thanks again!
June 1, 2011 at 8:07 am
SELECT
T.name,
STUFF((
SELECT
',' + name
FROM
sys.columns C
WHERE C.object_id = T.object_id
ORDER BY
name
FOR
XML PATH('')
) , 1 , 1 , '') As Columns_
FROM sys.tables T
ORDER BY name
June 1, 2011 at 8:16 am
Jeff - I am very interested in an RBAR-less solution
Declare @InFoodID int
SET @InFoodID = 2
SELECT
AnimalFood.FoodID, Animal.AnimalName
FROM
AnimalFood INNER JOIN
Animal ON AnimalFood.AnimalID = Animal.AnimalID
WHERE
(AnimalFood.FoodID = @InFoodID) AND
(AnimalFood.Deleted = 0) AND
(Animal.Deleted = 0)
ORDER BY Animal.AnimalName
This would yield:
FoodID AnimalName
2....... Smokey
2....... Spot
(I am not sure I need the FoodID in the results since the PK of AnimalFood is both AnimalID and FoodID)
Thanks in advance!
June 1, 2011 at 9:48 am
Ninja's_RGR'us (6/1/2011)
SELECTT.name,
STUFF((
SELECT
',' + name
FROM
sys.columns C
WHERE C.object_id = T.object_id
ORDER BY
name
FOR
XML PATH('')
) , 1 , 1 , '') As Columns_
FROM sys.tables T
ORDER BY name
Thanks Ninja, When I ran your script, I got the 'column' names concat'd. When I realized "the column names are the contents of the column", I came up with this:
DECLARE @InFoodID INT
SET @InFoodID = 2
SELECT STUFF((
SELECT
', ' + Animal.AnimalName
FROM
AnimalFood INNER JOIN
Animal ON AnimalFood.AnimalID = Animal.AnimalID
WHERE
(AnimalFood.FoodID = @InFoodID) AND
(AnimalFood.Deleted = 0) AND
(Animal.Deleted = 0)
ORDER BY Animal.AnimalName
FOR XML PATH('')
) , 1 , 2 , '') As Animals
This yeilds: "Smokey, Spot" which is what I want!!!
I see that FOR XML PATH does the concatination and STUFF removes the first comma.
Thanks for your help!
June 1, 2011 at 9:51 am
Bingo! Now you won't need us anymore!
June 1, 2011 at 11:27 pm
thomas.bassett (6/1/2011)
Jeff - I am very interested in an RBAR-less solution
Declare @InFoodID int
SET @InFoodID = 2
SELECT
AnimalFood.FoodID, Animal.AnimalName
FROM
AnimalFood INNER JOIN
Animal ON AnimalFood.AnimalID = Animal.AnimalID
WHERE
(AnimalFood.FoodID = @InFoodID) AND
(AnimalFood.Deleted = 0) AND
(Animal.Deleted = 0)
ORDER BY Animal.AnimalName
This would yield:
FoodID AnimalName
2....... Smokey
2....... Spot
(I am not sure I need the FoodID in the results since the PK of AnimalFood is both AnimalID and FoodID)
Thanks in advance!
No need. Remi beat me to it.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply