SQL Server Performance

please..... I want code

Discussion in 'T-SQL Performance Tuning for Developers' started by B5CSE, May 3, 2005.

  1. B5CSE New Member

    I have clients table that contain on the gender field
    And this field contain the id of gender 1 and 2 but I have another table(gender1)
    Which contain the id of gender and the name of gender male and female

    The problem I want transact statement that display all the fields in
    Clients table but the gender field display the name of gender in the
    Gender1 table not the id of gender which is saved in the clients table
  2. antares686 New Member

    Look at INNER JOIN in SQL Books On Line.
  3. derrickleggett New Member

    And tell the person designing the database that they are idiots.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  4. ranjitjain New Member

    Hi derrick be cool.

    Hi B5CSE,
    Is there a need to make another table to store two records for gender.
    U should have inserted gender name directly into client table.

    Anyways u can try this.
    Select c.clname,g.gname from
    client c inner join gender g on
    c.gid=g.gid
  5. dineshasanka Moderator

    Strange indeed,
    You can do this without joining the tables

    Select c.clname,Gender =
    case When GenderID = 1 then 'Male' else 'Female' end
    from
    client




    quote:Originally posted by B5CSE

    I have clients table that contain on the gender field
    And this field contain the id of gender 1 and 2 but I have another table(gender1)
    Which contain the id of gender and the name of gender male and female

    The problem I want transact statement that display all the fields in
    Clients table but the gender field display the name of gender in the
    Gender1 table not the id of gender which is saved in the clients table
  6. mmarovic Active Member

    quote:U should have inserted gender name directly into client table.
    No, he shouldn't.
  7. derrickleggett New Member

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] I love this discussion. lol We're going to all have to agree to disagree on this one. M,F,H,T,G,L,N That should about cover the gender list for you. <br /><br /><br /><br />.....Nevermind.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  8. ranjitjain New Member

    [:0]Gender field has few predefined values then why he should not normalize the database.<br />I dont c any necessity for that extra gender table.[<img src='/community/emoticons/emotion-11.gif' alt='8)' />]<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by mmarovic</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">U should have inserted gender name directly into client table.<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">No, he shouldn't.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />
  9. mmarovic Active Member

    quote:Select c.clname,Gender =
    case When GenderID = 1 then 'Male' else 'Female' end
    from
    client
    That is solution I support. Keep GenderId as a bit column . There is no need for lookup table. Even better return from sql genderID and interpret data on midle tier or client. Puting GenderName in Client table may be acceptable if table is small enough and not updated frequently but it is not realy necessary.
  10. dineshasanka Moderator

    derrick, <br />I know what M and F stand for<br />WHat are the others <br />[<img src='/community/emoticons/emotion-11.gif' alt='8)' />]<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by derrickleggett</i><br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />] M,F,H,T,G,L,N That should about cover the gender list for you. <br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
  11. ranjitjain New Member

    adding more to it[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br />G-Gays<br />L-Lesbians<br />N-None[xx(]<br />rest i have doubt<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by dineshasanka</i><br /><br />derrick, <br />I know what M and F stand for<br />WHat are the others <br />[<img src='/community/emoticons/emotion-11.gif' alt='8)' />]<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by derrickleggett</i><br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />] M,F,H,T,G,L,N That should about cover the gender list for you. <br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
  12. dineshasanka Moderator

    I guess that is not a gender it is a behaviour [<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by ranjitjain</i><br />G-Gays<br />L-Lesbians<br />N-None[xx(]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
  13. derrickleggett New Member

    M-Male (definitely gender)
    F-Female (definitely gender)
    H-Hermathrodite (definitely gender)
    T-Transgender (definitely gender)
    G-Gay (orientation)
    L-Lesbian (orientation)
    N-Neuter (this one could hurt. Gender????)

    A you need is M or F. You don't need a seperate table for that. If you see a column with M and F values called gender and you can't figure it out, you shouldn't be a DBA, programmer, or any profession requiring thinking. That's normalized to stupidity. If you want to have a drop-down box populated by a lookup table, then fine. Normalization is absolutely critical and extremely important. Taking it to that level though is just normalizing ad nauseam.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  14. dhilditch New Member

    Have a bit for gender, where 1 = true and 0 = false. For the column title, call it 'Superior' and have 1 indicate 'Male'! (light the touchpaper and stand back...)

    Small Businesses
    www.matiogi.com

Share This Page