October 21, 2009 at 1:29 am
My DB is for pet animals and one of the requirements is to use data from standard lists of species and breeds to create a variable 'breedName' for each pet, which shows if the animal is a purebreed or a cross breed, e.g.
a cross breed dog could have breedName = "Alsation X Labrador"
a pure breed dog could have breedName = "Labrador"
a bird cannot have a crossbreed, so e.g. breedName = "African Grey Parrot"
The following tables are involved:
dbo.species
speciesIDspeciesName
1Dog
2Cat
3Bird
4Rabbit
5Hamster
6Ferret
dbo.breeds
breedIDspeciesIDbreedName
11Labrador
21Alsatian
32Maine Coon
42Domestic Short Hair
53African Grey Parrot
63Falcon
dbo.petDetails
petID (INT)
speciesID (INT)
crossed (BIT) - this is either true or false to show if the animal is a crossbreed
breedPureID (INT)
breedCrossID (INT)
breedName (nvarchar(50)) - the column I want to populate
sample data
petIDspeciesIDcrossedbreedPureIDbreedCrossIDbreedName (desired results)
11112Labrador X Alsatian
2203NULLMaine Coon
3305NULLAfrican Grey Parrot
440NULLNULLRabbit
What I need help with is an insert/update trigger that gets the value of 'breedPureID' and 'breedCrossID' from dbo.breeds, concatenates the results and populates the 'breedName' column in petDetails. I think it should be something like this but I don't know how to get the values from the dbo.breeds for both breedpureID and breedCrossID:
UPDATE dbo.PetDetails
SET breedName =
CASE
WHEN speciesID < 3 AND crossed = 1 THEN 'value of breedPureID' + ' X ' + 'value of breedCrossID'
WHEN speciesID < 3 AND crossed = 0 THEN 'value of breedPureID'
WHEN speciesID = 3 then 'value of breedPureID'
WHEN speciesID > 3 then ' '
END
Can anyone help? Pretty Please?? Woof Woof?
😀
October 21, 2009 at 2:20 am
I love animals, so went an extra mile to provide the query 🙂
But if you provide the test data like this, you would get quicker reply. Thanks.
CREATE TABLE dbo.species(speciesID int, sepeciesName varchar(50))
INSERT INTO species values (1 , 'Dog')
INSERT INTO species values (2 , 'Cat')
INSERT INTO species values (3 , 'Bird')
INSERT INTO species values (4 , 'Rabbit')
INSERT INTO species values (5 , 'Hamster')
INSERT INTO species values (6 , 'Ferret')
CREATE TABLE dbo.breeds(breedID int, speciesID int, breedName varchar(50))
INSERT INTO dbo.breeds values (1, 1, 'Labrador' )
INSERT INTO dbo.breeds values (2, 1, 'Alsatian' )
INSERT INTO dbo.breeds values (3, 2, 'Maine Coon' )
INSERT INTO dbo.breeds values (4, 2, 'Domestic Short Hair' )
INSERT INTO dbo.breeds values (5, 3, 'African Grey Parrot' )
INSERT INTO dbo.breeds values (6, 3, 'Falcon' )
CREATE TABLE petDetails(petID INT,speciesID INT,
crossed BIT,breedPureID INT,breedCrossID INT,breedName nvarchar(50))
delete from petdetails
INSERT INTO petDetails Values (1 ,1 ,1 ,1 ,2, NULL)
INSERT INTO petDetails Values (2 ,2 ,0 ,3 ,NULL, NULL)
INSERT INTO petDetails Values (3 ,3 ,0 ,5 ,NULL, NULL)
INSERT INTO petDetails Values (4 ,4 ,0 ,NULL ,NULL, NULL)
Select * from petdetails
UPDATE PD
SET breedName =
CASE
WHEN PD.speciesID < 3 AND crossed = 1 THEN B1.BreedName + ' X ' + B2.BreedName
WHEN PD.speciesID < 3 AND crossed = 0 THEN B1.BreedNAME
WHEN PD.speciesID = 3 then B1.BreedName
WHEN PD.speciesID > 3 then ''
END -- Select *
FROM petdetails PD
LEFT JOIN breeds B1
ON PD.BreedPureID = B1.BreedID
LEFT Join Breeds B2
ON PD.BreedCrossID = B2.BreedID
LEFT JOIN species S
ON PD.speciesID = s.speciesID
Select * from petdetails
please see you have a condition where speciesID > 3 then you dont need a breedname. Your expected result is not reflecting that. Hope this helps.
---------------------------------------------------------------------------------
October 21, 2009 at 10:45 pm
Very many thanks indeed - works like a dream.
And I will remember the rules for posting my question next time!
🙂
Nick
October 21, 2009 at 11:50 pm
u r welcome 🙂
---------------------------------------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply