Danish Habib

Danish Habib

  • NA
  • 694
  • 233.4k

incorrect Result set of a simple SQL Query

Jan 22 2015 3:16 AM
I Have five tables
first table (users)
Users(userid(P),UserName,Email,Password,DistrictID)
UserAndDistricts(DistrictID(P),DistrictName,UserID(F))
UserAndTehsils(TehsilID(P),TEhsilName,UserID(F))
UserAndUcs(UCID(P),UCName,UserID(F))
UserAndVillage(VillageID(P),VillageName,UserID(F))


I have 12  user into the user table 
i have one record in district table mean all user belong to one district
I have three records in tehsil table I have three tehsil and all user belong to that 
I have 25 rows in ucs table 
I have 83 Rows in village table 
all table are saving the user id as well as foreign key but when i am using the below query it gives me 4150 rows why duplicating so much data 








SELECT  U.UserId,U.[Address],U.City,u.Country,UT.TehsilId,UC.UCID ,UC.UCName,UV.VillageId ,UV.VillageName  ,UT.TehsilName,U.DistrictId,UD.DistrictName ,UD.DistrictId ,UD.UserId,U.FullName,U.IPAddress,U.IsActive,U.IsAdmin,U.IsPasswordReset,U.IsReportable,U.IsVisible,U.CreateDate ,U.Mobile,U.Password,U.ReporintPerson,U.SecurityAnswer,U.SecurityQuestion ,U.UserId,U.Email,U.UserTypes FROM Users  U
RIGHT JOIN UserDistricts  UD ON UD.UserId=U.UserId
LEFT OUTER  JOIN UserTehsils UT ON UT.UserID=U.UserId 
LEFT OUTER JOIN UserUcs UC ON UC.UserID=U.UserId 
LEFT JOIN UserVillages UV ON UV.UserId=U.UserId 
WHERE U.UserId  =21

Answers (20)