SQL Query aproch? - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Query aproch?

Author

Topic

KunalPawar
Starting Member

1 Post

Posted - 2008-02-28 : 10:18:51

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.

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 don't feel this is an right way as i had this case in most of the tables in my DB.

Regards,
Kunal Pawar

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-28 : 10:32:03

Table design seems to be wrong.

You should have two tables - one for EducationLevel and another for EducationArea:

EducationLevel
EduLevelID Int PK
EduLevelName Varchar

EducationArea
EduAreaID Int PK
EduAreaName Varchar

and then you can have UserEducation like this:

UserEducation
Id int PK
EduLevelId Int FK
EduAreaID Int Fk
Center Varchar

and then you can simply join these three tables based on IDs.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-28 : 10:48:29

Or you can take query twice with table and retrieve results:-

SELECT ue.ID,
       l1.Name AS EducationLevel,
       l2.Name AS EducationArea,
       ue.Center
FROM UserEducation ue
INNER JOIN ListOption l1
ON l1.ID=ue.EducationLevel
INNER JOIN ListOption l2
ON l2.ID=ue.EducationArea

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources