incorrect Result set of a simple SQL Query

Question

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

Manish Kumar Choudhary · Answer

yes. User table should link to Districts and Districts table should link on tahil and so on tashil should link to village. Design your structure linke this.

Danish Habib · Answer

Dear Kumar I have made tables like that DistrictUsers(DistrictID(P)DistrictName) TehsilUsers(TehsilID(P)TehsilNameDistrictID(F)) UCSUSERS(UCID(P)UCNAMETehsilID(F)) VILLAGESUSER(VillageID(P)VillageNameUCID(F)) And I have one record in DistrictUser three records in TehsilUSers 23 Reoords in UCSUSERS 53 Records in VillagesUsers on Gridview editing below is my code SELECT U.UserIdU.[Address]U.Cityu.CountryUT.TehsilIdUC.UCID UC.UCNameUV.VillageId UV.VillageName UT.TehsilNameU.DistrictIdUD.DistrictName UD.DistrictId UD.UserIdU.FullNameU.IPAddressU.IsActiveU.IsAdminU.IsPasswordResetU.IsReportableU.IsVisibleU.CreateDate U.MobileU.PasswordU.ReporintPersonU.SecurityAnswerU.SecurityQuestion U.UserIdU.EmailU.UserTypes FROM Users U INNER JOIN UserDistricts UD ON UD.UserId=U.UserId INNER JOIN UserTehsils UT ON UT.UserID=U.UserId INNER JOIN UserUcs UC ON UC.UserID=U.UserId INNER JOIN UserVillages UV ON UV.UserId=U.UserId WHERE U.UserId =7 it return 3657 Rows Tell me please the mistake

Manish Kumar Choudhary · Answer

I don't think it will show down the process of your web application. This is how One To Many relationship work. You can find the associated District by using Select IdDistrictName from tblDistrict where TashilId= And display it to check box list.

Danish Habib · Answer

Dear I already have Checkboxlist for all these four entities and i have to option to select multiple items so i used checkboxlist and when i select select the tehsil how I find the associated district of that weather on district checkboxlist i have selected three items so which one associates to that tehsil  I have already a tables for maintaining hierarchy for that cascade then i have to give this tehsil id to dis districts table and find the associated district from there they may slow down the process of my web application

Manish Kumar Choudhary · Answer

In tashil entry form add one dropdown for District. The text field for that will be district and value filed of selected district will be Id of that District and save it. Just add that extra field there

Danish Habib · Answer

Dear As Now I have to make a Tehsil table like this TehsilId(P)TehsilNameDistrictID(F) (comming from District table) I have a question Now how to save the district id while saving the tehsil how to get the actual district id from which that tehsil belongs to

Danish Habib · Answer

I am going to do that Thanks for response and your time ..:)

Danish Habib · Answer

They are just related to user table should i have to create the relationship between them as well as in Tehsil table i have to place the district id The cascade nature is like this District->tehsils->Ucs-Villages u mean in last village table i have the above three forign keys as well like villageID VillagenameUCsIDTahsilidDistrict id

Manish Kumar Choudhary · Answer

Your table doesn't have proper relation among UserDistricts  UserTehsils and UserVillages so you are not getting proper result. Alter that first.

Danish Habib · Answer

USE [MISDB] GO /****** Object: Table [dbo].[Users] Script Date: 01/22/2015 15:06:22 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Users]( [UserId] [tinyint] NOT NULL [FullName] [nvarchar](50) NOT NULL [Email] [varchar](100) NOT NULL [Password] [nvarchar](200) NOT NULL [SecurityQuestion] [nvarchar](100) NOT NULL [SecurityAnswer] [nvarchar](100) NOT NULL [IsActive] [bit] NOT NULL [IsPasswordReset] [bit] NULL [IsAdmin] [bit] NOT NULL [IsVisible] [bit] NULL [IPAddress] [varchar](23) NULL [LastLoginDate] [datetime] NULL [CreateDate] [datetime] NOT NULL [UpdateDate] [datetime] NOT NULL [Mobile] [nvarchar](23) NULL [Address] [nvarchar](250) NULL [City] [nvarchar](250) NULL [Zip] [nvarchar](50) NULL [Country] [nvarchar](100) NULL [UserTypes] [tinyint] NULL [DistrictId] [tinyint] NULL [IsReportable] [bit] NULL [ReporintPerson] [tinyint] NULL CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ( [UserId] ASC )WITH (PAD_INDEX = OFF STATISTICS_NORECOMPUTE = OFF IGNORE_DUP_KEY = OFF ALLOW_ROW_LOCKS = ON ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT [dbo].[Users] ([UserId] [FullName] [Email] [Password] [SecurityQuestion] [SecurityAnswer] [IsActive] [IsPasswordReset] [IsAdmin] [IsVisible] [IPAddress] [LastLoginDate] [CreateDate] [UpdateDate] [Mobile] [Address] [City] [Zip] [Country] [UserTypes] [DistrictId] [IsReportable] [ReporintPerson]) VALUES (3 N'Admin' N'sweet.uet@gmail.com' N'pakistan123' N'What is your oldest nickname?' N'pakistan' 1 0 1 1 N'127.0.0.1' NULL CAST(0x0000A36E0066627C AS DateTime) CAST(0x0000A36E0066627C AS DateTime) NULL NULL NULL NULL NULL 1 1 0 NULL) INSERT [dbo].[Users] ([UserId] [FullName] [Email] [Password] [SecurityQuestion] [SecurityAnswer] [IsActive] [IsPasswordReset] [IsAdmin] [IsVisible] [IPAddress] [LastLoginDate] [CreateDate] [UpdateDate] [Mobile] [Address] [City] [Zip] [Country] [UserTypes] [DistrictId] [IsReportable] [ReporintPerson]) VALUES (5 N'test2' N'test2@teset2.com' N'pakistan' N'What is your oldest nickname?' N'pakistan' 1 0 0 1 N'::1' NULL CAST(0x0000A428004F912E AS DateTime) CAST(0x0000A428004F912E AS DateTime) NULL NULL NULL NULL NULL 4 1 0 NULL) INSERT [dbo].[Users] ([UserId] [FullName] [Email] [Password] [SecurityQuestion] [SecurityAnswer] [IsActive] [IsPasswordReset] [IsAdmin] [IsVisible] [IPAddress] [LastLoginDate] [CreateDate] [UpdateDate] [Mobile] [Address] [City] [Zip] [Country] [UserTypes] [DistrictId] [IsReportable] [ReporintPerson]) VALUES (6 N'go' N'go@go.com' N'pakistan' N'What is your oldest nickname?' N'pakistan' 1 0 0 1 N'::1' NULL CAST(0x0000A428006E7F3A AS DateTime) CAST(0x0000A428006E7F3A AS DateTime) NULL NULL NULL NULL NULL 4 1 0 NULL) /****** Object: Table [dbo].[UserVillages] Script Date: 01/22/2015 15:06:22 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[UserVillages]( [ID] [int] IDENTITY(11) NOT NULL [VillageId] [bigint] NULL [VillageName] [nvarchar](50) NULL [DateCreated] [smalldatetime] NULL [UserId] [tinyint] NULL CONSTRAINT [PK_UserVillages] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF STATISTICS_NORECOMPUTE = OFF IGNORE_DUP_KEY = OFF ALLOW_ROW_LOCKS = ON ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[UserVillages] ON INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (257 268 N'08-WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (258 274 N'10/WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (259 273 N'12/WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (260 269 N'12-WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (261 271 N'16/WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (262 275 N'168/E.B.' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (263 284 N'186-EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (264 283 N'188-EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (265 260 N'2/W.B. ret wala' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (266 280 N'200-EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (267 278 N'202-EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (268 281 N'204-EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (269 276 N'206-EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (270 279 N'212-EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (271 277 N'214-EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (272 282 N'216-EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (273 270 N'222-EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (274 259 N'23/W.B(1)' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (275 302 N'30-WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (276 290 N'37/W.B.' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (277 257 N'3WB ' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (278 258 N'3WB (E)' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (279 301 N'42-WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (280 300 N'44-WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (281 303 N'46-WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (282 294 N'477/E.B.' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (283 330 N'481-EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (284 304 N'48-WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (285 296 N'531/E.B.' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (286 292 N'541/EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (287 297 N'549/E.B.' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (288 295 N'553/E.B.' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (289 313 N'555/EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (290 310 N'559 EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (291 293 N'561/WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (292 308 N'563-EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (293 306 N'565-EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (294 307 N'569-EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (295 311 N'571-EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (296 309 N'575-EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (297 272 N'6/WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (298 242 N'62-KB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (299 247 N'63-KB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (300 248 N'63-KBn' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (301 246 N'64-KB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (302 256 N'65 KB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (303 254 N'65/K.B.' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (304 245 N'65-KB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (305 329 N'Basti Dingi Wala' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (306 240 N'Chajudeh' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (307 253 N'Chajudy' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (308 3 N'Chak No.10/WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (309 4 N'Chak No.12/WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (310 5 N'Chak No.14/EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (311 7 N'Chak No.16/EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (312 14 N'Chak No.160/EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (313 15 N'Chak No.162/EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (314 16 N'Chak No.164/EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (315 17 N'Chak No.166/EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (316 18 N'Chak No.168/EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (317 20 N'Chak No.172/EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (318 19 N'Chak No.174/EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (319 21 N'Chak No.176/WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (320 22 N'Chak No.178/WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (321 6 N'Chak No.18/EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (322 23 N'Chak No.180/WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (323 24 N'Chak No.182/WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (324 25 N'Chak No.184/WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (325 8 N'Chak No.20/EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (326 9 N'Chak No.22/EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (327 10 N'Chak No.222/EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (328 11 N'Chak No.224/EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (329 12 N'Chak No.226/EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (330 13 N'Chak No.228/EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (331 26 N'Chak No.541/WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (332 27 N'Chak No.549/WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (333 28 N'Chak No.553/WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (334 2 N'Chak No.6/WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (335 1 N'Chak No.8/WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (336 252 N'Chhajju Deh' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (337 244 N'Jehdo' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (338 249 N'Jhedu' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserVillages] ([ID] [VillageId] [VillageName] [DateCreated] [UserId]) VALUES (339 251 N'Tibbi Jhabela' CAST(0xA4280192 AS SmallDateTime) 6) SET IDENTITY_INSERT [dbo].[UserVillages] OFF /****** Object: Table [dbo].[UserUcs] Script Date: 01/22/2015 15:06:22 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[UserUcs]( [ID] [int] IDENTITY(11) NOT NULL [UCID] [bigint] NULL [UCName] [nvarchar](50) NULL [DateCreated] [smalldatetime] NULL [UserID] [tinyint] NULL CONSTRAINT [PK_UserUcs] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF STATISTICS_NORECOMPUTE = OFF IGNORE_DUP_KEY = OFF ALLOW_ROW_LOCKS = ON ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[UserUcs] ON INSERT [dbo].[UserUcs] ([ID] [UCID] [UCName] [DateCreated] [UserID]) VALUES (103 1 N'Chak No.16/EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserUcs] ([ID] [UCID] [UCName] [DateCreated] [UserID]) VALUES (104 2 N'Chak No.166/EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserUcs] ([ID] [UCID] [UCName] [DateCreated] [UserID]) VALUES (105 3 N'Chak No.537/WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserUcs] ([ID] [UCID] [UCName] [DateCreated] [UserID]) VALUES (106 31 N'Akbar Shah' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserUcs] ([ID] [UCID] [UCName] [DateCreated] [UserID]) VALUES (107 32 N'Chak 15 WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserUcs] ([ID] [UCID] [UCName] [DateCreated] [UserID]) VALUES (108 34 N'Chak 16 WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserUcs] ([ID] [UCID] [UCName] [DateCreated] [UserID]) VALUES (109 35 N'Chak 198 EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserUcs] ([ID] [UCID] [UCName] [DateCreated] [UserID]) VALUES (110 37 N'Chak 41 WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserUcs] ([ID] [UCID] [UCName] [DateCreated] [UserID]) VALUES (111 39 N'Chak 537 EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserUcs] ([ID] [UCID] [UCName] [DateCreated] [UserID]) VALUES (112 40 N'Chak 56 WB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserUcs] ([ID] [UCID] [UCName] [DateCreated] [UserID]) VALUES (113 41 N'Chak 561 EB' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserUcs] ([ID] [UCID] [UCName] [DateCreated] [UserID]) VALUES (114 45 N'Karim Wah' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserUcs] ([ID] [UCID] [UCName] [DateCreated] [UserID]) VALUES (115 46 N'Sharif Abad' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserUcs] ([ID] [UCID] [UCName] [DateCreated] [UserID]) VALUES (116 72 N'Alam Pur' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserUcs] ([ID] [UCID] [UCName] [DateCreated] [UserID]) VALUES (117 73 N'Ali Wah' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserUcs] ([ID] [UCID] [UCName] [DateCreated] [UserID]) VALUES (118 74 N'Buland Pur' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserUcs] ([ID] [UCID] [UCName] [DateCreated] [UserID]) VALUES (119 75 N'Chak 100 WB (Garha more)' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserUcs] ([ID] [UCID] [UCName] [DateCreated] [UserID]) VALUES (120 77 N'Shitab Garh' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserUcs] ([ID] [UCID] [UCName] [DateCreated] [UserID]) VALUES (121 78 N'Shah Sattar' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserUcs] ([ID] [UCID] [UCName] [DateCreated] [UserID]) VALUES (122 79 N'Sandhal' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserUcs] ([ID] [UCID] [UCName] [DateCreated] [UserID]) VALUES (123 80 N'Muhammad Shah' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserUcs] ([ID] [UCID] [UCName] [DateCreated] [UserID]) VALUES (124 81 N'Mailsi West' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserUcs] ([ID] [UCID] [UCName] [DateCreated] [UserID]) VALUES (125 82 N'Mailsi East' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserUcs] ([ID] [UCID] [UCName] [DateCreated] [UserID]) VALUES (126 83 N'Lali Pur' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserUcs] ([ID] [UCID] [UCName] [DateCreated] [UserID]) VALUES (127 84 N'Burana Khas' CAST(0xA4280192 AS SmallDateTime) 6) SET IDENTITY_INSERT [dbo].[UserUcs] OFF /****** Object: Table [dbo].[UserTehsils] Script Date: 01/22/2015 15:06:22 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[UserTehsils]( [ID] [int] IDENTITY(11) NOT NULL [TehsilId] [bigint] NULL [TehsilName] [nvarchar](50) NULL [DateCreated] [smalldatetime] NULL [UserID] [tinyint] NULL CONSTRAINT [PK_UserTehsils] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF STATISTICS_NORECOMPUTE = OFF IGNORE_DUP_KEY = OFF ALLOW_ROW_LOCKS = ON ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[UserTehsils] ON INSERT [dbo].[UserTehsils] ([ID] [TehsilId] [TehsilName] [DateCreated] [UserID]) VALUES (45 13 N'Vehari' CAST(0xA4280192 AS SmallDateTime) 6) INSERT [dbo].[UserTehsils] ([ID] [TehsilId] [TehsilName] [DateCreated] [UserID]) VALUES (46 14 N'Mailsi' CAST(0xA4280192 AS SmallDateTime) 6) SET IDENTITY_INSERT [dbo].[UserTehsils] OFF /****** Object: Table [dbo].[UserDistricts] Script Date: 01/22/2015 15:06:22 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[UserDistricts]( [Id] [int] IDENTITY(11) NOT NULL [DistrictId] [tinyint] NULL [DistrictName] [nvarchar](50) NULL [DateCreated] [smalldatetime] NULL [UserID] [tinyint] NULL CONSTRAINT [PK_UserDistricts] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF STATISTICS_NORECOMPUTE = OFF IGNORE_DUP_KEY = OFF ALLOW_ROW_LOCKS = ON ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[UserDistricts] ON INSERT [dbo].[UserDistricts] ([Id] [DistrictId] [DistrictName] [DateCreated] [UserID]) VALUES (22 1 N'Vehari' CAST(0xA4280192 AS SmallDateTime) 6) SET IDENTITY_INSERT [dbo].[UserDistricts] OFF /****** Object: Default [DF_UserDistricts_DateCreated] Script Date: 01/22/2015 15:06:22 ******/ ALTER TABLE [dbo].[UserDistricts] ADD CONSTRAINT [DF_UserDistricts_DateCreated] DEFAULT (getutcdate()) FOR [DateCreated] GO /****** Object: Default [DF_Users_IsPasswordReset] Script Date: 01/22/2015 15:06:22 ******/ ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_IsPasswordReset] DEFAULT ((0)) FOR [IsPasswordReset] GO /****** Object: Default [DF_Users_IsVisible] Script Date: 01/22/2015 15:06:22 ******/ ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_IsVisible] DEFAULT ((1)) FOR [IsVisible] GO /****** Object: Default [DF_UserTehsils_DateCreated] Script Date: 01/22/2015 15:06:22 ******/ ALTER TABLE [dbo].[UserTehsils] ADD CONSTRAINT [DF_UserTehsils_DateCreated] DEFAULT (getutcdate()) FOR [DateCreated] GO /****** Object: Default [DF_UserUcs_DateCreated] Script Date: 01/22/2015 15:06:22 ******/ ALTER TABLE [dbo].[UserUcs] ADD CONSTRAINT [DF_UserUcs_DateCreated] DEFAULT (getutcdate()) FOR [DateCreated] GO /****** Object: Default [DF_UserVillages_DateCreated] Script Date: 01/22/2015 15:06:22 ******/ ALTER TABLE [dbo].[UserVillages] ADD CONSTRAINT [DF_UserVillages_DateCreated] DEFAULT (getutcdate()) FOR [DateCreated] GO /****** Object: ForeignKey [FK_UserDistricts_Districts] Script Date: 01/22/2015 15:06:22 ******/ ALTER TABLE [dbo].[UserDistricts] WITH CHECK ADD CONSTRAINT [FK_UserDistricts_Districts] FOREIGN KEY([DistrictId]) REFERENCES [dbo].[Districts] ([DistrictId]) GO ALTER TABLE [dbo].[UserDistricts] CHECK CONSTRAINT [FK_UserDistricts_Districts] GO /****** Object: ForeignKey [FK_UserDistricts_Users] Script Date: 01/22/2015 15:06:22 ******/ ALTER TABLE [dbo].[UserDistricts] WITH CHECK ADD CONSTRAINT [FK_UserDistricts_Users] FOREIGN KEY([UserID]) REFERENCES [dbo].[Users] ([UserId]) GO ALTER TABLE [dbo].[UserDistricts] CHECK CONSTRAINT [FK_UserDistricts_Users] GO /****** Object: ForeignKey [FK_Users_Districts] Script Date: 01/22/2015 15:06:22 ******/ ALTER TABLE [dbo].[Users] WITH CHECK ADD CONSTRAINT [FK_Users_Districts] FOREIGN KEY([DistrictId]) REFERENCES [dbo].[Districts] ([DistrictId]) GO ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_Districts] GO /****** Object: ForeignKey [FK_UserTehsils_Users] Script Date: 01/22/2015 15:06:22 ******/ ALTER TABLE [dbo].[UserTehsils] WITH CHECK ADD CONSTRAINT [FK_UserTehsils_Users] FOREIGN KEY([UserID]) REFERENCES [dbo].[Users] ([UserId]) GO ALTER TABLE [dbo].[UserTehsils] CHECK CONSTRAINT [FK_UserTehsils_Users] GO /****** Object: ForeignKey [FK_UserUcs_Users] Script Date: 01/22/2015 15:06:22 ******/ ALTER TABLE [dbo].[UserUcs] WITH CHECK ADD CONSTRAINT [FK_UserUcs_Users] FOREIGN KEY([UserID]) REFERENCES [dbo].[Users] ([UserId]) GO ALTER TABLE [dbo].[UserUcs] CHECK CONSTRAINT [FK_UserUcs_Users] GO /****** Object: ForeignKey [FK_UserVillages_Users] Script Date: 01/22/2015 15:06:22 ******/ ALTER TABLE [dbo].[UserVillages] WITH CHECK ADD CONSTRAINT [FK_UserVillages_Users] FOREIGN KEY([UserId]) REFERENCES [dbo].[Users] ([UserId]) GO ALTER TABLE [dbo].[UserVillages] CHECK CONSTRAINT [FK_UserVillages_Users] GO

Manish Kumar Choudhary · Answer

Data Scripts also

Danish Habib · Answer

UserAndDistrict table : USE [MISDB] GO /****** Object: Table [dbo].[UserDistricts] Script Date: 01/22/2015 14:50:08 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[UserDistricts]( [Id] [int] IDENTITY(11) NOT NULL [DistrictId] [tinyint] NULL [DistrictName] [nvarchar](50) NULL [DateCreated] [smalldatetime] NULL [UserID] [tinyint] NULL CONSTRAINT [PK_UserDistricts] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF STATISTICS_NORECOMPUTE = OFF IGNORE_DUP_KEY = OFF ALLOW_ROW_LOCKS = ON ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[UserDistricts] WITH CHECK ADD CONSTRAINT [FK_UserDistricts_Districts] FOREIGN KEY([DistrictId]) REFERENCES [dbo].[Districts] ([DistrictId]) GO ALTER TABLE [dbo].[UserDistricts] CHECK CONSTRAINT [FK_UserDistricts_Districts] GO ALTER TABLE [dbo].[UserDistricts] WITH CHECK ADD CONSTRAINT [FK_UserDistricts_Users] FOREIGN KEY([UserID]) REFERENCES [dbo].[Users] ([UserId]) GO ALTER TABLE [dbo].[UserDistricts] CHECK CONSTRAINT [FK_UserDistricts_Users] GO ALTER TABLE [dbo].[UserDistricts] ADD CONSTRAINT [DF_UserDistricts_DateCreated] DEFAULT (getutcdate()) FOR [DateCreated] GO UserANDTEHSIL TABLE: USE [MISDB] GO /****** Object: Table [dbo].[UserTehsils] Script Date: 01/22/2015 14:50:56 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[UserTehsils]( [ID] [int] IDENTITY(11) NOT NULL [TehsilId] [bigint] NULL [TehsilName] [nvarchar](50) NULL [DateCreated] [smalldatetime] NULL [UserID] [tinyint] NULL CONSTRAINT [PK_UserTehsils] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF STATISTICS_NORECOMPUTE = OFF IGNORE_DUP_KEY = OFF ALLOW_ROW_LOCKS = ON ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[UserTehsils] WITH CHECK ADD CONSTRAINT [FK_UserTehsils_Users] FOREIGN KEY([UserID]) REFERENCES [dbo].[Users] ([UserId]) GO ALTER TABLE [dbo].[UserTehsils] CHECK CONSTRAINT [FK_UserTehsils_Users] GO ALTER TABLE [dbo].[UserTehsils] ADD CONSTRAINT [DF_UserTehsils_DateCreated] DEFAULT (getutcdate()) FOR [DateCreated] GO UserANDUCS TABLE: USE [MISDB] GO /****** Object: Table [dbo].[UserUcs] Script Date: 01/22/2015 14:51:20 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[UserUcs]( [ID] [int] IDENTITY(11) NOT NULL [UCID] [bigint] NULL [UCName] [nvarchar](50) NULL [DateCreated] [smalldatetime] NULL [UserID] [tinyint] NULL CONSTRAINT [PK_UserUcs] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF STATISTICS_NORECOMPUTE = OFF IGNORE_DUP_KEY = OFF ALLOW_ROW_LOCKS = ON ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[UserUcs] WITH CHECK ADD CONSTRAINT [FK_UserUcs_Users] FOREIGN KEY([UserID]) REFERENCES [dbo].[Users] ([UserId]) GO ALTER TABLE [dbo].[UserUcs] CHECK CONSTRAINT [FK_UserUcs_Users] GO ALTER TABLE [dbo].[UserUcs] ADD CONSTRAINT [DF_UserUcs_DateCreated] DEFAULT (getutcdate()) FOR [DateCreated] GO UserAndVillages table: USE [MISDB] GO /****** Object: Table [dbo].[UserVillages] Script Date: 01/22/2015 14:51:43 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[UserVillages]( [ID] [int] IDENTITY(11) NOT NULL [VillageId] [bigint] NULL [VillageName] [nvarchar](50) NULL [DateCreated] [smalldatetime] NULL [UserId] [tinyint] NULL CONSTRAINT [PK_UserVillages] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF STATISTICS_NORECOMPUTE = OFF IGNORE_DUP_KEY = OFF ALLOW_ROW_LOCKS = ON ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[UserVillages] WITH CHECK ADD CONSTRAINT [FK_UserVillages_Users] FOREIGN KEY([UserId]) REFERENCES [dbo].[Users] ([UserId]) GO ALTER TABLE [dbo].[UserVillages] CHECK CONSTRAINT [FK_UserVillages_Users] GO ALTER TABLE [dbo].[UserVillages] ADD CONSTRAINT [DF_UserVillages_DateCreated] DEFAULT (getutcdate()) FOR [DateCreated] GO User Table: USE [MISDB] GO /****** Object: Table [dbo].[Users] Script Date: 01/22/2015 14:52:05 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Users]( [UserId] [tinyint] NOT NULL [FullName] [nvarchar](50) NOT NULL [Email] [varchar](100) NOT NULL [Password] [nvarchar](200) NOT NULL [SecurityQuestion] [nvarchar](100) NOT NULL [SecurityAnswer] [nvarchar](100) NOT NULL [IsActive] [bit] NOT NULL [IsPasswordReset] [bit] NULL [IsAdmin] [bit] NOT NULL [IsVisible] [bit] NULL [IPAddress] [varchar](23) NULL [LastLoginDate] [datetime] NULL [CreateDate] [datetime] NOT NULL [UpdateDate] [datetime] NOT NULL [Mobile] [nvarchar](23) NULL [Address] [nvarchar](250) NULL [City] [nvarchar](250) NULL [Zip] [nvarchar](50) NULL [Country] [nvarchar](100) NULL [UserTypes] [tinyint] NULL [DistrictId] [tinyint] NULL [IsReportable] [bit] NULL [ReporintPerson] [tinyint] NULL CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ( [UserId] ASC )WITH (PAD_INDEX = OFF STATISTICS_NORECOMPUTE = OFF IGNORE_DUP_KEY = OFF ALLOW_ROW_LOCKS = ON ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[Users] WITH CHECK ADD CONSTRAINT [FK_Users_Districts] FOREIGN KEY([DistrictId]) REFERENCES [dbo].[Districts] ([DistrictId]) GO ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_Districts] GO ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_IsPasswordReset] DEFAULT ((0)) FOR [IsPasswordReset] GO ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_IsVisible] DEFAULT ((1)) FOR [IsVisible] GO

Manish Kumar Choudhary · Answer

Show your table scripts then I may solve it.

Danish Habib · Answer

Neither inner join nor left outer join works it still gives duplication

yashasvi Kumar · Answer

I hope inner join will work.. select us.Usernameus.emailUd.districtidud.DistrictNameut.TehsilIDut.TEhsilName uv.VillageIDuv.VillageName from users as us inner join UserAndDistricts as ud on us.userid=us.userid inner join UserAndTehsils as ut on us.userid=ut.userid inner join UserAndVillage as uv on us.userid=uv.userid where us.userid=21 Please Mark Ans if helpful

Manish Kumar Choudhary · Answer

use Left Outer Join

Danish Habib · Answer

Dear Thanks for you response I Have already Created a user page in which i have the form of userNameEmailPasswordSecurity QuestionSecurity AnswerDistrictTehsilUcsVillages For District tehsil uc and villages i have used the checkboxlist item and they are all populated based on their selection means they are cascade in nature (if you select the district then the associated Tehsils would be populated and if you select the tehsil the associated ucs would be populated and if you select the tehsil the associated ucs would be populated and on selction of the Ucs the associated villages would be populated . they are good at population side Now i want to store the records for basic information of the user i have created a user table as i mentioned previouslly and for District tehsil ucs and villages i created separate table for all these four and passing the user id as foreign key Now they are saving correct but while retriveing the record in gridview edit mode when I fetching the record it gives me records in 4000 weather i jsut have one district associated with that user 2 tehsil associated with that user 24 ucs associated with that user 83 vilalges associated with that user below is the query which i want to execuate but it gives me not correct result set SELECT U.UserIdU.[Address]U.Cityu.CountryUT.TehsilIdUC.UCID UC.UCNameUV.VillageId UV.VillageName UT.TehsilNameU.DistrictIdUD.DistrictName UD.DistrictId UD.UserIdU.FullNameU.IPAddressU.IsActiveU.IsAdminU.IsPasswordResetU.IsReportableU.IsVisibleU.CreateDate U.MobileU.PasswordU.ReporintPersonU.SecurityAnswerU.SecurityQuestion U.UserIdU.EmailU.UserTypes FROM Users U LEFT JOIN UserDistricts UD ON UD.UserId=U.UserId LEFT JOIN UserTehsils UT ON UT.UserID=U.UserId LEFT JOIN UserUcs UC ON UC.UserID=U.UserId LEFT JOIN UserVillages UV ON UV.UserId=U.UserId WHERE U.UserId =21 I have used right left inner join all but not solved

yashasvi Kumar · Answer

Mean to say that you want to create a list or view of district tahseel.. if users belong that or not

Danish Habib · Answer

I want to extract all the districts their id and name (From District Table) all the Tehsil (idname) from Tehsils table  all the ucs (uciducname) from the uc table and all the vaillages(villageidvillagename) from villages table I already have a foreign key on all these table (userid is the foreign key coming form master table user where it is primary key) because i have checkboxlist item based on the result set i have to checked the checkboxlist item based on reslut set I have used inner join outer join right left no one works for me

yashasvi Kumar · Answer

what result you desired as output