Multiple columns
-
Hello, Please advise on how I might be able to make this sproc work... declare @TrainerID int select TrainerID, LastName, FirstName, City, State, Country, r.code, l.description, p.Code from trainers as t inner join Regions as r on t.region = r.regionid inner join Languages as l on t.Language1 = l.LangID --and t.Language2 = l.LangID --and t.Language3 = l.LangID inner join products as p on t.product1 = p.ProductID --on t.product2 = p.ProductID --on t.product3 = p.ProductID --on t.product4 = p.ProductID where TrainerID = @TrainerID The situation is that I have designed in the Trainers (t) table multiple columns of Languages and Products with properties as Int which stores values based on their respective LanguageID and ProductID from the Languages and Products (definition) table. The dilemma is on the result, I would like to see the actual codes and not the ID key of each of the multiple columns coming from the definition tables. Thanks Jay Feng Screwed - the art of moving to a new place because the old place is just too messy to clean.
-
Hello, Please advise on how I might be able to make this sproc work... declare @TrainerID int select TrainerID, LastName, FirstName, City, State, Country, r.code, l.description, p.Code from trainers as t inner join Regions as r on t.region = r.regionid inner join Languages as l on t.Language1 = l.LangID --and t.Language2 = l.LangID --and t.Language3 = l.LangID inner join products as p on t.product1 = p.ProductID --on t.product2 = p.ProductID --on t.product3 = p.ProductID --on t.product4 = p.ProductID where TrainerID = @TrainerID The situation is that I have designed in the Trainers (t) table multiple columns of Languages and Products with properties as Int which stores values based on their respective LanguageID and ProductID from the Languages and Products (definition) table. The dilemma is on the result, I would like to see the actual codes and not the ID key of each of the multiple columns coming from the definition tables. Thanks Jay Feng Screwed - the art of moving to a new place because the old place is just too messy to clean.
Use multiple inner joins ...
INNER JOIN Languages as l1 ON t.Language1 = l1.LangID INNER JOIN Languages as l2 ON t.Language2 = l2.LangID INNER JOIN Languages as l3 ON t.Language3 = l3.LangID...etc.
My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More