Help please joining tables in update query

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

General SQL Server Forums

New to SQL Server Programming

Help please joining tables in update query

Author

Topic

nick_dkc
Starting Member

25 Posts

Posted - 2009-10-21 : 03:19:57

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
speciesID speciesName
1 Dog
2 Cat
3 Bird
4 Rabbit
5 Hamster
6 Ferret

dbo.breeds
breedID speciesID breedName
1 1 Labrador
2 1 Alsatian
3 2 Maine Coon
4 2 Domestic Short Hair
5 3 African Grey Parrot
6 3 Falcon

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

petID speciesID Cross breedPureID breedCrossID breedName (desired results)
1 1 1 1 2 Labrador X Alsatian
2 2 0 3 NULL Maine Coon
3 3 0 5 NULL African Grey Parrot
4 4 0 NULL NULL Rabbit

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-21 : 12:10:33

[code]update pd
set pd.breedName=coalesce(coalesce(b1.breedName+ 'X','') + coalesce(b2.breedName,''),s.speciesName)
from dbo.petDetails pd
join dbo.species s
on s.speciesID=pd.speciesID
left join dbo.breeds b1
on b1.speciesID=s.speciesID
and b1.breedID=pd.breedPureID
left join dbo.breeds b2
on b2.speciesID=s.speciesID
and b2.breedID=breedCrossID
[/code]