incorrect Result set of a simple SQL Query

Danish Habib

11y1.5k

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

20 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manish Kumar Choudhary11y ago
Accepted 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 Habib11y ago
Dear Kumar I have made tables like that
DistrictUsers(DistrictID(P),DistrictName)
TehsilUsers(TehsilID(P),TehsilName,DistrictID(F))
UCSUSERS(UCID(P),UCNAME,TehsilID(F))
VILLAGESUSER(VillageID(P),VillageName,UCID(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.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
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 Choudhary11y ago
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 Id,DistrictName from tblDistrict where TashilId=

And display it to check box list.
Danish Habib11y ago
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 Choudhary11y ago
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 Habib11y ago
Dear As Now I have to make a Tehsil table like this
TehsilId(P),TehsilName,DistrictID(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 Habib11y ago
I am going to do that Thanks for response and your time ..:)
Danish Habib11y ago
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 Villagename,UCsID,Tahsilid,District id
Manish Kumar Choudhary11y ago
Your table doesn't have proper relation among UserDistricts , UserTehsils and UserVillages so you are not getting proper result.

Alter that first.
Danish Habib11y ago
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'[email protected]', 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'[email protected]', 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'[email protected]', 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(1,1) 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(1,1) 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(1,1) 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(1,1) 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 Choudhary11y ago
Data Scripts also
Danish Habib11y ago
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(1,1) 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(1,1) 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(1,1) 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(1,1) 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 Choudhary11y ago
Show your table scripts then I may solve it.
Danish Habib11y ago
Neither inner join nor left outer join works it still gives duplication
yashasvi Kumar11y ago
I hope inner join will work..

select us.Username,us.email,Ud.districtid,ud.DistrictName,ut.TehsilID,ut.TEhsilName
uv.VillageID,uv.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 Choudhary11y ago
use Left Outer Join
Danish Habib11y ago
Dear Thanks for you response I Have already Created a user page in which i have the form of userName,Email,Password,Security Question,Security Answer,District,Tehsil,Ucs,Villages
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.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
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 Kumar11y ago
Mean to say that you want to create a list or view of district, tahseel.. if users belong that or not
Danish Habib11y ago
I want to extract all the districts their id and name (From District Table) all the Tehsil (id,name) from Tehsils table , all the ucs (ucid,ucname) from the uc table and all the vaillages(villageid,villagename) 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 Kumar11y ago
what result you desired as output