incorrect records from two tables using join

Question

I have two tables first table QuestionTarget whose columns are ( [Id] [bigint] IDENTITY(01) 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(11) 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

Pankaj Kumar Choudhary · Answer

SELECT sum(cte.TARGET) target  sum(cte.ACHIEVED) achieved FROM (SELECT SUM(TargetValue) AS TARGET SUM(AchievedValue) AS ACHIEVED FROM First F INNER JOIN Second AS S ON S.QuestionID=F.QuestionID Where 1=1 group by S.RecordID)cte

Danish Habib · Answer

it is giving the wrong result and meanwhile it does not allow to apply where clause

Krishna Murthy · Answer

Hi Select Sum(Target) from FirstTable Union Select Sum(Archived) from SecondTable

Danish Habib · Answer

I want SUM OF BOTH TARGET AND ACHIEVED  QUESTIONID(P) TARGET 1 200 2 300 Second table RecordID(P) QuestionID(F) Achieved 1 1 10 2 1 200 3 2 300 I WANT SOME OF TARGET and SUM OF ACHIVED like SUM OF TARGET SHOUD BE (500) and SUM OF ACHIEVED SHOULD BE (510)

Danish Habib · Answer

Table First QuestionID(primaryKey) TargetValue 1 100 2 500 3 200 Second Table RecordID(primaryKey) Achieved Value QuestionID(foreignkey) 1 14 1 2 14 1 3 16 2 As per Manas told Please find below query if you want to get 100: SELECT SUM(Achieved) AS ACHIEVED [Target] FROM tabl1 INNER JOIN tabl2 on tabl2.QuestionID=tabl1.QuestionID group by tabl1.QuestionID tabl1.Target 1-I want to calculate SUM of Target and SUm of achieved not just target and sum of achieved I want while calculating SUM of Target SELECT SUM(QuestionTarget) will takes 100 regardless of how many rows in the second table against question 01 .... if inner join create problem i have tried left outer join as well it also not giving the desired result

Krishna Murthy · Answer

Hi Danish your query is wrong. with your expectation below query would suit your requirments SELECT F.QuestionID TargetValue  Sum(Archived Value) FROM FirstTable F INNER JOIN SecondTable S ON F.QuestionID = S.QuestionID Where F.QuestionID = 1 Group by F.QuestionID TargetValue Order by F.QuestionID

Danish Habib · Answer

First TAble QuestionID Target Value Men Women Boy Girl CITY 1 200 100 25 25 50 LAHORE 2 400 LAHORE Second Table QuestionID(Foreignkey) Achived Value Village 1 100 First 1 200 Second The problem is that i want to get the sum of Target and sum of achieved values and also want to give the parameters @QuestionID for which i want the sum in crystal report the issue is that if i have one row in first table for question ID 1 and two rows in second table for question ID 1 when i want to calculate the sum of Target vs sum of achived my Answer table has two rows so due to inner join it return two rows and it adds my target value two times for question id =1 SELECT SUM(ISNULL(0TargetValue) from First AS F INNER JOIN Second AS S ON S.QuestionID=F.QuestionID so my Target value sum becomes 400 instead of just 200 due to two rows in second table ..

Krishna Murthy · Answer

SELECT mt.ID COUNT(d.MasterID) as [#Count] FROM MasterTable mt INNER JOIN Detail d on mt.ID = d.ID GROUP BY mt.ID ORDER BY mt.ID

Danish Habib · Answer

My issue is just when i join using inner join the join append the values for all rows comming from second table so the sum is incorrect mean that if i have 30 rows in second table and one row in the first table and when i inner join all the thirty rows gets value of first row from the first table

Danish Habib · Answer

I have tried listen to me once more I have a table in which i have a target value for user that they have to achieve this target for example Build new schools in village  I give them a target of 100 for whole City  Now he/she has 300 villages in this city so he has to submit his achivement against these targets so 300 rows when i inner join and try to make the sum of target value vs sum of achieved value all 300 rows in achived table gets value of 100 so how to get that ...

Krishna Murthy · Answer

Hi Dhanish SELECT mt.ID mt.NAME mt.AGE COUNT(d.MasterID) as [#Subjects] FROM MasterTable mt LEFT OUTER JOIN Detail d on mt.ID = d.ID GROUP BY mt.ID mt.NAME mt.AGE ORDER BY mt.ID Hope this might help you. If it suits your requirement pls mark it as answered.

Sumit Joshi · Answer

Hi Danish Please share the Query that is not showing relevent results