We help IT Professionals succeed at work.

Need to return the value of the ids in the query.

omegalove
omegalove asked
on
The GenderID should return Male

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER   PROCEDURE [dbo].[tbl_Profile_Select]
      @sUserName nvarchar(50)
AS
SELECT  

ProfileID as ProfileID,
UserName  as UserName,
HeadLine  as HeadLine,
GenderID  as Gender,

FROM [dbo].[tbl_Profile]
WHERE
      [UserName] = 'cupryk'

6-14-2010-2-00-52-AM.png
Comment
Watch Question

Database Developer and Administrator
Commented:

SELECT  

ProfileID as ProfileID,
UserName  as UserName,
HeadLine  as HeadLine,
CASE GenderID WHEN 1 THEN 'Male' WHEN 2 THEN 'Female' END as Gender

FROM [dbo].[tbl_Profile]
WHERE
      [UserName] = 'cupryk'

Open in new window

Author

Commented:
the thing Is that how can I make
the following
REFERENCES type_lookup (name);
[GenderID] [int] NOT NULL CONSTRAINT [DF_tbl_Profile_GenderID]  DEFAULT ((1)),

CREATE TABLE [dbo].[tbl_Profile](
      [ProfileID] [int] IDENTITY(1,1) NOT NULL,
      [UserID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [UserName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [HeadLine] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [GenderID] [int] NOT NULL CONSTRAINT [DF_tbl_Profile_GenderID]  DEFAULT ((1)),
      [SeekingID] [int] NOT NULL CONSTRAINT [DF_tbl_Profile_SeekingID]  DEFAULT ((1)),

Author

Commented:
The the thing is there I need the value from the table.
I would appreciate any help.
6-14-2010-2-48-43-AM.png
Rajkumar GsSoftware Engineer
Commented:
Do you mean this query ?

Raj
SELECT a.GenderID, b.Gender --, Select other required columns here
FROM tbl_Profile a
	LEFT OUTER JOIN tbl_lookup_gender b
		ON a.GenderID = b.GenderID

Open in new window

Rajkumar GsSoftware Engineer
Commented:
Your original query can be modified like this

Raj
SELECT  

ProfileID as ProfileID,
UserName  as UserName,
HeadLine  as HeadLine,
a.GenderID  as Gender,
b.Gender  as GenderName

FROM [dbo].[tbl_Profile] a
	LEFT OUTER JOIN tbl_lookup_gender b
		ON a.GenderID = b.GenderID
WHERE
      [UserName] = 'cupryk'

Open in new window

Author

Commented:
should I create a reference from tbl_profile to tbl_lookup_gender.

Author

Commented:
why we doing left outter join?

Author

Commented:
Do I need the constraints on the lookup tables?
Top Expert 2010
Commented:
you will need the other table to have just 2 columns and just 2 records

for such a case i wont create a lookuptable for the gender, i would go with the solution suggested by @agux3e

unless you will be adding more records to teh gender table. any way see below is another option of getting the same result without using joins (use nested select, but make sure you have 1 record per GenderID, other use use Select Top 1.)

just another option if you dont want to do joins

SELECT  
a.ProfileID as ProfileID,
a.UserName  as UserName,
a.HeadLine  as HeadLine,
(select b.Gender from tbl_lookup_gender b where a.GenderID = b.GenderID) as Gender
FROM [dbo].[tbl_Profile] a
WHERE a. [UserName] = 'cupryk'
Rajkumar GsSoftware Engineer
Commented:
I agree with Ammar's comment. Since Gender information is limited, you can avoid using another lookup table. So the best way is to avoid lookup table and use query using CASE that agux3e demonstrated.

>> why we doing left outter join?
'Left Outer Join' will return all records from main table, even if there is no related records in lookup table. Means even if main table have no gender information, the result will show NULL in the column related from lookup table

For lookup table, just create GenderID as primary key and set foreign key with main table.

Suggested method is avoid lookup table for non-changing data.

Raj

Author

Commented:
Hi amar how can I fix this?

SELECT  
ProfileID as ProfileID,
UserName  as UserName,
HeadLine  as HeadLine,
a.GenderID  as Gender,
b.Gender  as GenderName,
a.SeekingID as Seeking,
c.Seeking as SeekingName,
SearchAgeStartID as StartAge,
SearchAgeEndID as EndAge,
Birthdate as BirthDate,
ZipCode as ZipCode,
a.CountryID as Country,
d.Country as CountryName,
a.RegionID AS Region,
dbo.fnRegions_GetRegionName(a.RegionID, d.CountryID) as RegionName,
a.CityID AS CityName,

a.AppearanceID as Appearance,
e.Appearance as AppearanceName,

a.BodyTypeID as BodyType,
f.BodyType as BodyTypeName,

a.StyleID AS Style,
g.Style AS StyleName,

a.ComplexionID AS Complexion,
h.Complexion AS ComplexionName,

a.EyeColorID AS EyeColor,
i.EyeColor AS EyeColorName,

a.EyeWearID AS EyeWear,
j.EyeWear AS EyeWearName,

a.FacialHairID AS FacialHair,
k.FacialHair AS FacialHairName,

a.FacialShapeID AS FacialShape,
l.FacialShape AS FacialShapeName,

a.FashionSenseID AS FashionSense,
m.FashionSense AS FashionSenseName,

a.BestFeatureID AS BestFeature,
n.BestFeature AS BestFeatureName,

a.HairColorID AS HairColor,
o.HairColor AS HairColorName,

a.HairStyleID AS HairStyle,
p.HairStyle AS HairStyleName,

a.HeightID AS Height,
q.Height AS HeightName,

a.WeightID AS Weight,
r.Weight AS WeightName,

a.PiercingID AS Piercing,
s.Piercing AS PiercingName,
--
--a.TattooID AS Tattoo,
--t.Tattoo AS TattooName,
--
--a.JewelryID AS Jewelry,
--u.Jewelry AS JewelryName,
--
--a.EthnicityID AS Ethnicity,
--v.Ethnicity AS EthnicityName,
--
--a.SmokingID AS Smoking,
--w.Smoking AS SmokingName,
--
--a.DrinkingID AS Drinking,
--x.Drinking AS DrinkingName,
--
--a.DrugID AS Drug,
--y.Drug AS DrugName,
--
--a.DietID AS Diet,
--z.Diet AS DietName,
--
--a.DisabilityID AS Disability,
--aa.Disability AS DisabilityName,
--
--a.ReligionID AS Religion,
--ab.Religion AS ReligionName,
--
--a.NationalityID AS Nationality,
--ac.Nationality AS NationalityName,
--
--a.EducationID AS Education,
--ad.Educaton AS EducationName,
--
--a.OccupationID AS Occupation,
--ae.Occupation AS OccupationName,
--
--a.EmploymentStatusID AS EmploymentStatus,
--af.EmploymentStatus AS EmploymentStatusName,
--
--a.HaveChildrenID AS HaveChildren,
--ag.HaveChildren AS HaveChildrenName,
--
--a.WantChildrenID AS WantChildren,
--ah.WantChildren AS WantChildrenName,
--
--a.MotherTongueID AS MotherTongue,
--ai.MotherTongue AS MotherTongueName,
--
--a.IncomeID AS Income,
--aj.Income AS IncomeName,
--
--a.Latitude AS Latitude,
--a.Longitude AS Longitude,
--a.IPAddress AS IPAddress,
--
--a.SenseHumorID AS SenseHumor,
--ak.SenseHumor AS SenseHumorName,
--
--a.SpendingHabitID AS SpendingHabit,
--al.SpendingHabit AS SpendingHabitName,
--
--a.RelocateID AS Relocate,
--am.Relocate AS RelocateName,
--
--a.TransportationID AS Transportation,
--an.Transportation AS TransportationName,
--
--a.StatusID AS MaritalStatus,
--ao.Status AS MaritalStatusName,

a.AboutMe AS AboutMeText,
a.SearchFor AS SearchForText,
a.IdealDate AS IdealDateText,
a.FearMeeting AS FearMeetingText,
a.MeHappy AS MeHappyText,

a.Casual_Shortterm CasualShortTermCheck,
a.Email AS EmailCheck,
a.Friends AS FriendsCheck,
a.LongTerm AS LongTermCheck,
a.Marriage AS MarriageCheck,
a.PhysicalIntimate AS PhysicalIntimateCheck

FROM [dbo].[tbl_Profile] a
LEFT OUTER JOIN  tbl_lookup_gender b
  ON a.GenderID = b.GenderID
Left Outer Join  tbl_lookup_seeking c
  ON a.SeekingID = c.SeekingID
Left Outer Join  CountryCodes d
  ON a.CountryID = d.CountryID
Left Outer Join  tbl_lookup_appearance e
  ON a.AppearanceID = e.AppearanceID
Left Outer Join  tbl_lookup_bodytype f
  ON a.BodyTypeID = f.BodyTypeID
Left Outer Join  tbl_lookup_style g
  ON a.StyleID = g.StyleID
Left Outer Join  tbl_lookup_complexion h
  ON a.ComplexionID = h.ComplexionID
Left Outer Join  tbl_lookup_eyecolor i
  ON a.EyeColorID = i.EyeColorID
Left Outer Join  tbl_lookup_eyewear j
  ON a.EyeWearID = j.EyeWearID
Left Outer Join  tbl_lookup_facialhair k
  ON a.FacialHairID = k.FacialHairID
Left Outer Join  tbl_lookup_facialshape l
  ON a.FacialShapeID = l.FacialShapeID  
Left Outer Join  tbl_lookup_fashionsense m
  ON a.FashionSenseID = m.FashionSenseID
Left Outer Join  tbl_lookup_bestfeature n
  ON a.BestFeatureID = n.BestFeatureID  
Left Outer Join  tbl_lookup_haircolor o
  ON a.HairColorID = o.HairColorID  
Left Outer Join  tbl_lookup_hairstyle p
  ON a.HairStyleID = p.HairStyleID    
Left Outer Join  tbl_lookup_height q
  ON a.HeightID = p.HeightID  
Left Outer Join  tbl_lookup_weight r
  ON a.WeightID = p.WeightID
Left Outer Join  tbl_lookup_piercing s
  ON a.PiecingID = s.PiecingID
    
WHERE
      [UserName] = 'cupryk'

Open in new window

Top Expert 2010
Commented:
hi @omegalove

from your script i understand that you have a main table [tbl_Profile] and all other tables are just lookup tables.

so in this case each lookup table should have a primary key and few other columns
and in the main table [tbl_Profile] you should have a foreign key to each of these lookups

and if your database is designed properly and these tables have their relations configured properly.

dont use left out join, just use  inner join, its more efficient.

or use nested select as i mentioned in earlier script.

i hope i am helping






SELECT  
a.ProfileID as ProfileID,
a.UserName  as UserName,
a.HeadLine  as HeadLine,
a.GenderID  as Gender,
b.Gender  as GenderName,
a.SeekingID as Seeking,
c.Seeking as SeekingName,
SearchAgeStartID as StartAge,
SearchAgeEndID as EndAge,
Birthdate as BirthDate,
ZipCode as ZipCode,
a.CountryID as Country,
d.Country as CountryName,
a.RegionID AS Region,
dbo.fnRegions_GetRegionName(a.RegionID, d.CountryID) as RegionName,
a.CityID AS CityName,

a.AppearanceID as Appearance,
e.Appearance as AppearanceName,

a.BodyTypeID as BodyType,
f.BodyType as BodyTypeName,

a.StyleID AS Style,
g.Style AS StyleName,

a.ComplexionID AS Complexion,
h.Complexion AS ComplexionName,

a.EyeColorID AS EyeColor,
i.EyeColor AS EyeColorName,

a.EyeWearID AS EyeWear,
j.EyeWear AS EyeWearName,

a.FacialHairID AS FacialHair,
k.FacialHair AS FacialHairName,

a.FacialShapeID AS FacialShape,
l.FacialShape AS FacialShapeName,

a.FashionSenseID AS FashionSense,
m.FashionSense AS FashionSenseName,

a.BestFeatureID AS BestFeature,
n.BestFeature AS BestFeatureName,

a.HairColorID AS HairColor,
o.HairColor AS HairColorName,

a.HairStyleID AS HairStyle,
p.HairStyle AS HairStyleName,

a.HeightID AS Height,
q.Height AS HeightName,

a.WeightID AS Weight,
r.Weight AS WeightName,

a.PiercingID AS Piercing,
s.Piercing AS PiercingName,

a.AboutMe AS AboutMeText,
a.SearchFor AS SearchForText,
a.IdealDate AS IdealDateText,
a.FearMeeting AS FearMeetingText,
a.MeHappy AS MeHappyText,

a.Casual_Shortterm CasualShortTermCheck,
a.Email AS EmailCheck,
a.Friends AS FriendsCheck,
a.LongTerm AS LongTermCheck,
a.Marriage AS MarriageCheck,
a.PhysicalIntimate AS PhysicalIntimateCheck

FROM [dbo].[tbl_Profile] a
inner JOIN  tbl_lookup_gender b  ON a.GenderID = b.GenderID
inner Join  tbl_lookup_seeking c  ON a.SeekingID = c.SeekingID
inner Join  CountryCodes d  ON a.CountryID = d.CountryID
inner Join  tbl_lookup_appearance e  ON a.AppearanceID = e.AppearanceID
inner Join  tbl_lookup_bodytype f  ON a.BodyTypeID = f.BodyTypeID
inner Join  tbl_lookup_style g  ON a.StyleID = g.StyleID
inner Join  tbl_lookup_complexion h  ON a.ComplexionID = h.ComplexionID
inner Join  tbl_lookup_eyecolor i  ON a.EyeColorID = i.EyeColorID
inner Join  tbl_lookup_eyewear j  ON a.EyeWearID = j.EyeWearID
inner Join  tbl_lookup_facialhair k  ON a.FacialHairID = k.FacialHairID
inner Join  tbl_lookup_facialshape l  ON a.FacialShapeID = l.FacialShapeID  
inner Join  tbl_lookup_fashionsense m  ON a.FashionSenseID = m.FashionSenseID
inner Join  tbl_lookup_bestfeature n  ON a.BestFeatureID = n.BestFeatureID  
inner Join  tbl_lookup_haircolor o  ON a.HairColorID = o.HairColorID  
inner Join  tbl_lookup_hairstyle p  ON a.HairStyleID = p.HairStyleID    
inner Join  tbl_lookup_height q ON a.HeightID = p.HeightID  
inner Join  tbl_lookup_weight r  ON a.WeightID = p.WeightID
inner Join  tbl_lookup_piercing s  ON a.PiecingID = s.PiecingID
    
WHERE
      a.[UserName] = 'cupryk'

Open in new window

Author

Commented:
Raj in the image above ID: 32982411
GenderID as primary key and set foreign key with main table.

It is a one to one relationship?

Why does it show many to one from tbl_profile to tbl_lookup_gender ?

Author

Commented:
By the way the inner join is super fast.

Nice and clear.

Author

Commented:
Raj or Amar in the image above ID: 32982411
GenderID as primary key and set foreign key with main table.

It is a one to one relationship? Should I have that relationship?

Why does it show many to one from tbl_profile to tbl_lookup_gender ?
Rajkumar GsSoftware Engineer
Commented:
>> It is a one to one relationship?
Why does it show many to one from tbl_profile to tbl_lookup_gender ?

It is one-to-many relationship, since there may be more than one record in main table using the same gender record.

Raj
Top Expert 2010
Commented:
it should be one to many

i mean the main table tbl_profile has many records for the genderid field

but in the tbl_lookup_gender there is only 1 record for the genderid field

usually for lookup tables you have one to many relation ship, thats the whole point of a lookup table.

is that you dont repeat the text male, male, male you just repeat 1, 1, 1

because in typing male you might mistake it to mael, or mole, etc

thats why lookup tables are for dropdown list, to minimize mistakes and make your data intgrerity strong.

hope it clears

Author

Commented:
the image shows one to many?

tbl_profile        |     tlb_lookup_gender
GenderID 1     |        1                                Male
                     |        2                               Female

Author

Commented:
Awesome job guys.