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.
| Author |
Topic |
|
swathigardas
Posting Yak Master
149 Posts |
 Posted - 2008-10-31 : 07:15:27
|
| Hi i have Query as shown below.It returns duplicates too... i've pasted the result set below.. But Guess its not formatetd properly..... can u tell me whats wrong in the below QuerySELECT distinct a.Address_NBR , a.Address1_TXT ,a.Address2_TXT ,a.City_NBR ,c.City_NM ,a.State_NBR ,s.State_NM ,co.Country_NBR ,co.Country_NM ,a.PinCode_TXT ,a.Phone_NBR ,ea.Employee_Address_NBR ,ea.Address_Type ,ei.Employee_Info_NBR ,ei.Personal_Email_Addr ,ei.Residential_Phn_NBR ,ei.Mobile_NBR ,fb.Family_Background_NBR ,fb.Relation_NM ,fb.Emergency_Contact_NBR ,fb.Notes_TXT FROM TEmployee e left JOIN TEmployee_Info ei ON e.Employee_NBR = ei.Employee_NBR left JOIN TFamily_Background fb ON e.Employee_NBR = fb.Employee_NBR AND fb.Active_FG=0 AND fb.Is_Emergency_FG=1 left JOIN TEmployee_Address ea ON e.Employee_NBR = ea.Employee_NBR left JOIN TAddress a ON a.Address_NBR = ea.Address_NBR AND a.Active_FG=0 left JOIN TCity c ON c.City_NBR = a.City_NBR left JOIN TState s ON s.State_NBR = a.State_NBR left JOIN TCountry co ON s.Country_NBR = co.Country_NBR WHERE e.Employee_NBR = 193Address_NBR Address1_TXT Address2_TXT City_NBR City_NM State_NBR State_NM Country_NBR Country_NM PinCode_TXT Phone_NBR Employee_Address_NBR Address_Type Employee_Info_NBR Personal_Email_Addr Residential_Phn_NBR Mobile_NBR Family_Background_NBR Relation_NM Emergency_Contact_NBR Notes_TXT----------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------- ----------- -------------------------------------------------- ----------- -------------------------------------------------- ----------- -------------------- -------------------- ------------ ----------------- ------------------------------ -------------------- -------------------- --------------------- -------------------------------------------------- --------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------47 cybergateway 27 Delhi 27 Puducherry 1 India NULL 11 Present 71 NULL NULL NULL 19 fdfgdfgfdg 45454 fdfgdf48 cusbertowers 27 Delhi 27 Puducherry 1 India NULL 12 Present 71 NULL NULL NULL 19 fdfgdfgfdg 45454 fdfgdf54 hgf 27 Delhi 27 Puducherry 1 India 5675676 NULL 13 Present 71 NULL NULL NULL 19 fdfgdfgfdg 45454 fdfgdf47 cybergateway 27 Delhi 27 Puducherry 1 India NULL 11 Present 71 NULL NULL NULL 20 fdhgggggggggggggggggggggg 88980 fdfgdfjksdfhsdjfhsdjkfhsdkjfhsdjkfhsdjkhsdkj48 cusbertowers 27 Delhi 27 Puducherry 1 India NULL 12 Present 71 NULL NULL NULL 20 fdhgggggggggggggggggggggg 88980 fdfgdfjksdfhsdjfhsdjkfhsdkjfhsdjkfhsdjkhsdkj54 hgf 27 Delhi 27 Puducherry 1 India 5675676 NULL 13 Present 71 NULL NULL NULL 20 fdhgggggggggggggggggggggg 88980 fdfgdfjksdfhsdjfhsdjkfhsdkjfhsdjkfhsdjkhsdkj47 cybergateway 27 Delhi 27 Puducherry 1 India NULL 11 Present 71 NULL NULL NULL 21 ghgfh 565 gfhgf48 cusbertowers 27 Delhi 27 Puducherry 1 India NULL 12 Present 71 NULL NULL NULL 21 ghgfh 565 gfhgf54 hgf 27 Delhi 27 Puducherry 1 India 5675676 NULL 13 Present 71 NULL NULL NULL 21 ghgfh 565 gfhgf47 cybergateway 27 Delhi 27 Puducherry 1 India NULL 11 Present 71 NULL NULL NULL 22 ghgfh 645645 bnbvnbv48 cusbertowers 27 Delhi 27 Puducherry 1 India NULL 12 Present 71 NULL NULL NULL 22 ghgfh 645645 bnbvnbv54 hgf 27 Delhi 27 Puducherry 1 India 5675676 NULL 13 Present 71 NULL NULL NULL 22 ghgfh 645645 bnbvnbv47 cybergateway 27 Delhi 27 Puducherry 1 India NULL 11 Present 71 NULL NULL NULL 23 ghgfh 645645 bnbvnbv48 cusbertowers 27 Delhi 27 Puducherry 1 India NULL 12 Present 71 NULL NULL NULL 23 ghgfh 645645 bnbvnbv54 hgf 27 Delhi 27 Puducherry 1 India 5675676 NULL 13 Present 71 NULL NULL NULL 23 ghgfh 645645 bnbvnbv(15 row(s) affected) |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-10-31 : 08:03:55
|
quote:
Originally posted by swathigardas
Hi i have Query as shown below.It returns duplicates too... i've pasted the result set below.. But Guess its not formatetd properly..... can u tell me whats wrong in the below QuerySELECT distinct a.Address_NBR , a.Address1_TXT ,a.Address2_TXT ,a.City_NBR ,c.City_NM ,a.State_NBR ,s.State_NM ,co.Country_NBR ,co.Country_NM ,a.PinCode_TXT ,a.Phone_NBR ,ea.Employee_Address_NBR ,ea.Address_Type ,ei.Employee_Info_NBR ,ei.Personal_Email_Addr ,ei.Residential_Phn_NBR ,ei.Mobile_NBR ,fb.Family_Background_NBR ,fb.Relation_NM ,fb.Emergency_Contact_NBR ,fb.Notes_TXT FROM TEmployee e left JOIN TEmployee_Info ei ON e.Employee_NBR = ei.Employee_NBR left JOIN TFamily_Background fb ON e.Employee_NBR = fb.Employee_NBR AND fb.Active_FG=0 AND fb.Is_Emergency_FG=1 left JOIN TEmployee_Address ea ON e.Employee_NBR = ea.Employee_NBR left JOIN TAddress a ON a.Address_NBR = ea.Address_NBR AND a.Active_FG=0 left JOIN TCity c ON c.City_NBR = a.City_NBR left JOIN TState s ON s.State_NBR = a.State_NBR left JOIN TCountry co ON s.Country_NBR = co.Country_NBR WHERE e.Employee_NBR = 193Address_NBR Address1_TXT Address2_TXT City_NBR City_NM State_NBR State_NM Country_NBR Country_NM PinCode_TXT Phone_NBR Employee_Address_NBR Address_Type Employee_Info_NBR Personal_Email_Addr Residential_Phn_NBR Mobile_NBR Family_Background_NBR Relation_NM Emergency_Contact_NBR Notes_TXT----------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------- ----------- -------------------------------------------------- ----------- -------------------------------------------------- ----------- -------------------- -------------------- ------------ ----------------- ------------------------------ -------------------- -------------------- --------------------- -------------------------------------------------- --------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------47 cybergateway 27 Delhi 27 Puducherry 1 India NULL 11 Present 71 NULL NULL NULL 19 fdfgdfgfdg 45454 fdfgdf48 cusbertowers 27 Delhi 27 Puducherry 1 India NULL 12 Present 71 NULL NULL NULL 19 fdfgdfgfdg 45454 fdfgdf54 hgf 27 Delhi 27 Puducherry 1 India 5675676 NULL 13 Present 71 NULL NULL NULL 19 fdfgdfgfdg 45454 fdfgdf47 cybergateway 27 Delhi 27 Puducherry 1 India NULL 11 Present 71 NULL NULL NULL 20 fdhgggggggggggggggggggggg 88980 fdfgdfjksdfhsdjfhsdjkfhsdkjfhsdjkfhsdjkhsdkj48 cusbertowers 27 Delhi 27 Puducherry 1 India NULL 12 Present 71 NULL NULL NULL 20 fdhgggggggggggggggggggggg 88980 fdfgdfjksdfhsdjfhsdjkfhsdkjfhsdjkfhsdjkhsdkj54 hgf 27 Delhi 27 Puducherry 1 India 5675676 NULL 13 Present 71 NULL NULL NULL 20 fdhgggggggggggggggggggggg 88980 fdfgdfjksdfhsdjfhsdjkfhsdkjfhsdjkfhsdjkhsdkj47 cybergateway 27 Delhi 27 Puducherry 1 India NULL 11 Present 71 NULL NULL NULL 21 ghgfh 565 gfhgf48 cusbertowers 27 Delhi 27 Puducherry 1 India NULL 12 Present 71 NULL NULL NULL 21 ghgfh 565 gfhgf54 hgf 27 Delhi 27 Puducherry 1 India 5675676 NULL 13 Present 71 NULL NULL NULL 21 ghgfh 565 gfhgf47 cybergateway 27 Delhi 27 Puducherry 1 India NULL 11 Present 71 NULL NULL NULL 22 ghgfh 645645 bnbvnbv48 cusbertowers 27 Delhi 27 Puducherry 1 India NULL 12 Present 71 NULL NULL NULL 22 ghgfh 645645 bnbvnbv54 hgf 27 Delhi 27 Puducherry 1 India 5675676 NULL 13 Present 71 NULL NULL NULL 22 ghgfh 645645 bnbvnbv47 cybergateway 27 Delhi 27 Puducherry 1 India NULL 11 Present 71 NULL NULL NULL 23 ghgfh 645645 bnbvnbv48 cusbertowers 27 Delhi 27 Puducherry 1 India NULL 12 Present 71 NULL NULL NULL 23 ghgfh 645645 bnbvnbv54 hgf 27 Delhi 27 Puducherry 1 India 5675676 NULL 13 Present 71 NULL NULL NULL 23 ghgfh 645645 bnbvnbv(15 row(s) affected)
-------------Charlie |
 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
|
|
|
|
|
|
|
Query returning duplicates t