Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have table CLIENT which have columns are

COLUMN_NAME DATA_TYPE DESCRIPTION SOURCE TABLE

ClientPK         int    Identity column                 
ClientID         int    Client ID           PARTY   
ClientTypeID  int   Client type ID          PARTY   
FirstName     varchar(128)  First name      PARTY_DETAILS   
MiddleName    varchar(128)  Middle name     PARTY_DETAILS       
LastName         varchar(128)   Last name       PARTY_DETAILS       
FullName         varchar(256)   Full name       PARTY_DETAILS       
DateOfBirth   datetime  Date of birth       PARTY_DETAILS       
YearOfBirth   smallint  Year of birth                   
MaritalStatus varchar(20)   Marital status      PARTY_DETAILS       
ClientGenderName    varchar(10) Gender name PARTY_DETAILS       
ClientGenderPK  int  Gender PK      ClientGenders   

And table PARTY_DETAILS which have columns

PARTY_ID    int Party unique ID
FIRST_NAME  varchar(128)    First name
MIDDLE_NAME varchar(128)    Middle name
FAMILY_NAME varchar(128)    Last name
NAME    varchar(256)    Full name
DOB datetime    Date of birth
MARITAL_STATUS  varchar(20) Marital status
GENDER  varchar(10) Gender

And Party table

COLUMN_NAME      DATA_TYPE
PARTY_ID    int 
PARTY_TYPE_ID   int 

I want to write a query to Validate every client has a valid gender? Could anybody help me?

share|improve this question
2  
I don't see any link between tables to validate this. And you appear to have both key and value in lookup table in the CLIENT table. Can you add more info and some data please –  gbn May 10 '13 at 9:22
    
thanks for reply. For client table, source table is PARTY and PARTY_DETAILS. Thats why I have given these 2 tables just for refrence. So based on these table just I want to write a query which will validate client has valid gender or not? –  ramu May 10 '13 at 11:27
add comment

2 Answers

The table(s) seem to need some normalization. For example, in the Client table there seems to be both foreign key and data it's being pointed to.

First you need to define what values are valid for gender. Is male/female sufficient? Are abbreviations like m/f allowed? Depending on your locale, there might be additional genders for LGBT people, maybe even one for 'Will not tell'. What's the meaning of a NULL value in this column?

After you know what the range for valid values are, filtering invalid set is quite simple. This assumes you are really using denormalized table, so no join is needed.

SELECT ClientID 
FROM Client 
WHERE ClientGenderName NOT IN ('male', 'female', 'whatever')

For future use, consider adding CHECK constraint to the ClientGenderName column. This prevents inserting rows that don't contain valid values in the first hand. While you are at it, you should consider adding NOT NULL constraints too.

ALTER TABLE ClientID 
ADD CONSTRAINT CK_ValidGender 
CHECK (ClientGenderName in ('valid', 'genders', 'here'))
share|improve this answer
    
Thanks VonPryz ... It works exactly as I wanted....I have one more question plz help me out to solve this...Thanks in advance...Link for that question is... dba.stackexchange.com/questions/42125/… –  ramu May 14 '13 at 10:26
add comment

Since I'm not sure I fully understand your question as the normalization in your sample tables seems to be a bit weird, this might not be what you're looking for.

But assuming you're trying to check of all the data in CLIENT.ClientGenderName matches PARTY_DETAILS.Gender, you can simply use a query like so:

SELECT * 
FROM CLIENT
WHERE ISNULL(ClientGenderName,'') NOT IN 
   (SELECT ISNULL(Gender,'') FROM PARTY_DETAILS)

This returns any rows in CLIENT where ClientGenderName has a value that's not found in PARTY_DETAILS table's Gender column.

share|improve this answer
    
Thanks Kahn ...It works exactly as I wanted....I have one more question plz help me out to solve this...Thanks in advance...Link for that question is... dba.stackexchange.com/questions/42125/… –  ramu May 14 '13 at 5:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.