Danish Habib

Danish Habib

  • NA
  • 694
  • 246.1k

incorrect records from two tables using join

Aug 19 2015 1:16 AM
I have two tables 
first table
QuestionTarget whose columns are
(
[Id] [bigint] IDENTITY(0,1) NOT NULL,
[QT_ID] [bigint] NOT NULL,
[Question_ID] [int] NOT NULL,
[QuestionTarget] [bigint] NULL,
[QT_FormMen] [bigint] NULL,
[QT_ForWomen] [bigint] NULL,
[QT_ForGirl] [bigint] NULL,
[QT_ForBoy] [bigint] NULL,
[User_Id] [tinyint] NULL,
[District] [tinyint] NULL,
[CommunityCodes] [tinyint] NULL,
[Year] [tinyint] NULL,
[Quarter] [tinyint] NULL,
[User_Name] [nvarchar](50) NULL,
[User_Types] [tinyint] NULL,
[IsActive] [bit] NULL,
[SiteCultureId] [tinyint] NULL,
[IsSubmitted] [bit] NULL,
[IsReseted] [bit] NULL,
[Status] [char](1) NULL,
[Comments] [nvarchar](500) NULL,
[AttemptCount] [bigint] NULL,
[DateCreated] [smalldatetime] NULL,
[DateModified] [smalldatetime] NULL,
CONSTRAINT [PK_QuestionTarget] PRIMARY KEY CLUSTERED
)
second Table AnswersNew(
[id] [bigint] IDENTITY(1,1) NOT NULL,
[AnswerID] [bigint] NOT NULL,
[Question_ID] [int] NULL,
[QTotal] [bigint] NULL,
[QT_ForMen] [bigint] NULL,
[QT_ForWomen] [bigint] NULL,
[QT_ForBoy] [bigint] NULL,
[QT_ForGirl] [bigint] NULL,
[CDF_ID] [tinyint] NULL,
[CdfPErsonName] [nvarchar](50) NULL,
[DistrictId] [tinyint] NULL,
[UserTypes] [tinyint] NULL,
[Year] [tinyint] NULL,
[Quarter] [tinyint] NULL,
[CommunityCodes] [tinyint] NULL,
[CDPDeveloped] [bit] NULL,
[CDF_Comments] [nvarchar](2000) NULL,
[CDC_Comments] [nvarchar](2000) NULL,
[Reporting_Name] [nvarchar](50) NULL,
[IsActive] [bit] NULL,
[LastLoginDate] [smalldatetime] NULL,
[IsVisible] [bit] NULL,
[IsFinalized] [bit] NULL,
[AttemptCount] [bigint] NULL,
[Status] [char](1) NULL,
[VillagesId] [bigint] NULL,
[Date_Created] [smalldatetime] NULL,
[Date_Modified] [smalldatetime] NULL,
CONSTRAINT [PK_AnswersNew] PRIMARY KEY CLUSTERED
)
Now listen me In my first table i am saving the Target value for question and when i insert one row in first table the second table should contain 16 rows for one row in first table so if i insert four rows in first table then in second table there would be 16*4 Rows , Now i want to calculate the sum of a column from second table and the issue is that when i used inner join it takes value from first table for all 16 rows and make a total of it weather it must take one value because against one record there are 16 records in second table so inner join gives values to all 16 rows  

Answers (12)