Duplicate Record in join

Question

I have two tables QuestionTarget in which I have save the Target of question and Answer table in which i have Achieved value of that target .
Question Target Table
QuestionID,Targetvalue
1 55
2 67

Answer Table
AnswerID Question01 AchievedValue Question02 AchievedValue Question03 Achieved Value

Now I want to get the Target from the target table and AchievedValue from the answer table

My Query is this
"SELECT A.AnswerId,CdfPErsonName, A.Year, A.Quarter, QT.QuestionTarget,A.Question01Total,Question01 FROM
Answers AS A
LEFT JOIN
QuestionTarget AS QT ON QT.Question_ID=A.Question01 Where QT.Question_ID=1"

I also used LEFT OUTER JOIN INNER JOIN RIGHT JOIN NO ONE WORK THE PROBLEM IS THAT
below is the record set which comes from the query
Question01 QuestionTarget QuestionAchieved
1 53 12
2 77 12

The question Target Columns are good they are saved as 53,77 but in answer table i have save answer of 12 against 53 then why it is pulling 12 against 77 also that is the question of the week

Danish Habib · Answer

Dear Joginder Banger Thanks For Supports Here i have to post a new thread if i need to attach a file if possible the n give me your email i will send you there whole the discription along with snapshots you gave me a good database structure in which i will have primary foreign key relationship  the problem with me is that i made a wrong data base structure for question and their responses tables and now lot of customer has input the data into the database  I do not want to make the mega changes into the database but i have to create reports so I need fo fetch the data from database .Now below are the mentioned issues 1-I have Users into my system and question in my system and these question have target values (like i have assigned you a city in which your target is to open 46 new schools for child) Now user has to report each year how many schools did he build against the target value like 46. below is the database structure  I also have the question master table where the question id and actual question text is stored and QuestionTargettable(questionIDTargetvalue) and Answer table(Question01itsanswerquestion02itsanswerquestion03itsanswer-------) I already now that i will have 16 question  so by this structure i am unable to make each question(like question01question02-----) as foreign key so i need to get the target value from the QuestionTarget table and answer value from the answer table  I have assign Question01 Target for 2 years for first year i have assign 44 and for second year i have assigned 78 and USer just respond me for first year target mean (he respond me against 44) Now when i am going to make any of join between these two table the first row comes correctly (Remember i have 2 entries in QuestionTarget table for Q1 and one entry in answer table for Q1) when i am using that query  select Q.QuestionTarget A.Question01Total Q.Question_ID from QuestionTarget Q left join Answers A on Q.Question_ID =A.Question01 with of whthout where clause (where Q.Question_ID =1) it return two rows in first it get the actual target and answer value but for second row the target is present on that database but the answer is not present (but it show the same answer for second target value as for first target value ) like QuestionTarget QuestionAnswerQuestionID it shows these (4431) (5531) weather i just have 3 for 44 not for 55 in answer table

Joginder Banger · Answer

why are using where condition on your query..

Joginder Banger · Answer

try this code... create table #tempquestion (id intquestion_respondid int) create table #answertable (id int answer_respone int) insert into #tempquestion (idquestion_respondid)values(120)(221) insert into #answertable(idanswer_respone)values(112) select Q.idQ.question_respondidA.answer_respone from #tempquestion Q left join #answertable A on q.id=A.id

Joginder Banger · Answer

wait a some moment i create one question as per your request.

Danish Habib · Answer

Dear Joginder Banger Thanks for contineous responses  I appreciate your replies What you wrote in your last reply is correctly the same as my data base but the question is that using both of the queries you provided I am getting the correct target value as below QuestionTarget( I have Two entries here for Question01)for example i have target of year 2001 (45) and year of 2002 (67) against question01 and in answer table i just have one value against target let say i have achieved value 22 against 45(2001 Target value) but when the query is executed it gives two rows having correct target values but when it found that the target value 67 has no result in asnwer table it input the same achieved value (like 22) for second target too even that this is not inserted into the answer table it take that from the first value (against 45 target valeu)

Danish Habib · Answer

Dear Joginder Banger Thanks for contineous responses  I appreciate your replies What you wrote in your last reply is correctly the same as my data base but the question is that using both of the queries you provided I am getting the correct target value as below QuestionTarget( I have Two entries here for Question01)for example i have target of year 2001 (45) and year of 2002 (67) against question01 and in answer table i just have one value against target let say i have achieved value 22 against 45(2001 Target value) but when the query is executed it gives two rows having correct target values but when it found that the target value 67 has no result in asnwer table it input the same achieved value (like 22) for second target too even that this is not inserted into the answer table it take that from the first value (against 45 target valeu)

Joginder Banger · Answer

I assume in your question table have this data Question Table ID QuestionTarget 1 53 2 77 Answer Table id Answer 1 12 if you want show data have match the id answer table try this select * from questionTable Q inner join Answertable A on Q.id=A.ID where Q.id=1 second Condition if you want show the questiontable complete data and answer match only. select * from questionTable Q left join Answertable A on Q.id=A.ID where Q.id=1

Joginder Banger · Answer

Dear if you have not Primary key and foreign key this is bad idea a for you. Your database have lot of duplicate data. if your wish is no need join both table. No problem. show your answer table data once again.

Danish Habib · Answer

Dear I do not have a primary key or foreign key relationship I have QuestionTarget Table and Answer Table . The scenario is that each question has target value against this target value the user has to answer below is the data base diagram of question target table QuestionTarget Table: QuestionID QuestionTarget 1 67 2 66 Answer Table Question01(means the ID of Question) AchievedValue 1 12 Now I want to Select The Target Value from the QuestionTarget table and and Achievedvalue from the answer table What whould be the query ? I wrote the query but it gives main correct questiontarget (From Question target table ) but wronge achieved Value from the answer table I also need to give the QuestionID as paramater for which question i want to get its reslut I wrote the query like that SELECT A.AnswerIdCdfPErsonName A.Year A.Quarter QT.QuestionTargetA.Question01TotalQuestion01 FROM Answers AS A LEFT JOIN QuestionTarget AS QT ON QT.Question_ID=A.Question01 Where QT.QuestionID =1 -------------------------- Question01(contains the question ID 1 )

Joginder Banger · Answer

Am if right assume you are create the primary key QT.Question_ID and Foreign Key A.Question01. if above condition are true then you are using following query. Note:- One Condition if you want show both are table have question id same. SELECT A.AnswerIdCdfPErsonName A.Year A.Quarter QT.QuestionTargetA.Question01TotalQuestion01 FROM Answers AS A Inner Join QuestionTarget AS QT ON QT.Question_ID=A.Question01 Where QT.Question_ID=1 condition second:- SELECT A.AnswerIdCdfPErsonName A.Year A.Quarter QT.QuestionTargetA.Question01TotalQuestion01 FROM Answers AS A right Join QuestionTarget AS QT ON QT.Question_ID=A.Question01 Where QT.Question_ID=1

Joginder Banger · Answer

Hi Habib your question is not clear. You are create the confusion just rearrange question. which link create you are between both table means that i have no idea in which column behalf you are join. have any primary key and foreign key in both table.