incorrect records from two tables using join

Question

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  

Answer 1

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

Answer 2

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

Answer 3

Hi,

 

Select Sum(Target) from FirstTable

Union

Select Sum(Archived) from SecondTable 

Answer 4

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) 

Answer 5

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 

Answer 6

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

 

Answer 7

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(0,TargetValue) 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 .. 

Answer 8

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

Answer 9

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 

Answer 10

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 ...

Answer 11

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.

Answer 12

Hi Danish,

 

Please share the Query that is not showing relevent results