February 23, 2008 at 5:50 am
Hello all,
I have following tables as
ListOption
ID Int PK
Name Varchar
UserEducation
Id int PK
EducationLevel Int FK
EducationArea Int Fk
Center Varchar
Here EducationLevel and EducationArea are foreign keys from ListOption table.
now while selecting data from UserEducation table i want that FK Id's should be replaced with the Name from ListOption table.
Can anyone suggest me the optimize way to archive this result.
Thanks in advance.
Regards,
Kunal Pawar.
February 23, 2008 at 10:46 am
you have to write a query that joins the tables together.
This sounds like homework or an exam question. If you show some work in writing a query, but we can help you with the details.
February 25, 2008 at 1:53 am
Here i had created one function for returning the value of the Name from ListOption table.
CREATE FUNCTION [dbo].[GetListOptionDetails]
(
@fListOptionID int
)
RETURNS nvarchar(255)
AS
BEGIN
RETURN (SELECT Name from ListOption where Id = @fListOptionID)
END
SELECT id, starSuiteUserId
, EducationLevel, dbo.[GetListOption](EducationLevel) as EducationAreaDetails
, EducationArea, dbo.[GetListOption](EducationArea) as EducationAreaDetails
,Center
FROM UserEducation
WHERE id = @id;
i had return this query but i dont feel this is an right way as i had this case in most of the tables in my DB.
March 7, 2008 at 2:24 pm
kunal (2/25/2008)
Here i had created one function for returning the value of the Name from ListOption table.CREATE FUNCTION [dbo].[GetListOptionDetails]
(
@fListOptionID int
)
RETURNS nvarchar(255)
AS
BEGIN
RETURN (SELECT Name from ListOption where Id = @fListOptionID)
END
SELECT id, starSuiteUserId
, EducationLevel, dbo.[GetListOption](EducationLevel) as EducationAreaDetails
, EducationArea, dbo.[GetListOption](EducationArea) as EducationAreaDetails
,Center
FROM UserEducation
WHERE id = @id;
i had return this query but i dont feel this is an right way as i had this case in most of the tables in my DB.
No, this is an unneccesary use of functions.
There are several ways to do this, which is the fastest depends on a number of different things, but in general you usually will not see too much different between them.
What I would call the canonical SQL-92 approach would be:
SELECT id, starSuiteUserId
, EducationLevel
, (SELECT Name from ListOption where Id = EducationLevel) as LevelDetails
, EducationArea
, (Select Name from ListOption where Id = EducationArea) as AreaDetails
,Center
FROM UserEducation
WHERE id = @id;
You can of course achieve the same thing with the more common appraoch of using Joins:
SELECT id, starSuiteUserId
, EducationLevel
, LL.Name as LevelDetails
, EducationArea
, LA.Name as AreaDetails
,Center
FROM UserEducation U
Join ListOption LL ON (LL.Id = EducationLevel)
Join ListOption LA ON (LA.Id = EducationArea)
WHERE id = @id;
-- RBarryYoung
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply