SQL Query returning repeating information

Question

I'm having an issue where this query statement is giving me repeating GoalText in the results. Any ideas?

The complete query statement:

Select  g.GoalText, convert(nvarchar, g.GoalID) + '|' + convert(nvarchar, r.GoalReqID) as GoalID, GoalReqID
from Goal g inner join GoalRequirement r
on g.GoalID = r.GoalID 
where GoalReqID in
  (Select GoalReqID
  from GoalRequirement r inner join SurveyAnswer a
   on r.QuestionID = a.QuestionID and  ReqQuestionValue = SurveyAnswer 
   where a.CycleID = 93 and ReqBMILevel is null
  and ReqEnergyBalance is null and SurveyAnswer = 1 and r.QuestionID in
    (Select  QuestionID from Question where QuestionParent = 6000));

Results:

GoalText   GoalID GoalReqID
Choose lasagna, ravioli, stuffed pasta 1-3 times a week instead of 4 or more times a week.  13|442 442
Choose macaroni and cheese 1-3 times a week instead of 4 or more times a week.  14|443 443
Choose meats in gravies- beef stew, chicken pot pie,… 1-3 times a week instead of 4 or more times a week.   15|444 444
Choose spaghetti, meat sauce and/or meatballs, 1-3 times a week instead of 4 or more times a week.  16|445 445
Choose tacos, burritos, enchiladas, nachos with meat and/or cheese 1-3 times a week instead of 4 or more times a week.  17|446 446
Choose biscuits and sausage gravy 1-3 times a week instead of 4 or more times a week.   102|482 482
Choose pizza- all types and calzones 1-3 times a week instead of 4 or more times a week.    12|483 483
Choose hamburger/tuna/chicken noodle casseroles (includes “Helper”) 1-3 times a week instead of 4 or more times a week. 130|484 484
Choose lasagna, ravioli, stuffed pasta 1-3 times a week instead of 4 or more times a week.  13|485 485
Choose macaroni and cheese 1-3 times a week instead of 4 or more times a week.  14|486 486
Choose meats in gravies- beef stew, chicken pot pie,… 1-3 times a week instead of 4 or more times a week.   15|487 487
Choose spaghetti- marinara sauce only, 1-3 times a week instead of 4 or more times a week.  132|488 488
Choose spaghetti, meat sauce and/or meatballs, 1-3 times a week instead of 4 or more times a week.  16|489 489
Choose tacos, burritos, enchiladas, nachos with meat and/or cheese 1-3 times a week instead of 4 or more times a week.  17|490 490

The individual queries that make up the query:

Select  g.GoalText, convert(nvarchar, g.GoalID) + '|' + convert(nvarchar, r.GoalReqID) as GoalID, GoalReqID
from Goal g inner join GoalRequirement r
on g.GoalID = r.GoalID 

Results: There are 444 records in this query, but you should get the idea.

GoalText   GoalID GoalReqID
Eat an additional 400-500 calories per day. 1|1 1
Choose fried potatoes, French fries, hash browns, potato salad 1-3 times a week instead of 4 or more times a week.  2|2 2
Choose fried potatoes, French fries, hash browns, potato salad 1-3 times a week instead of 4 or more times a week.  2|106 106
Choose fried potatoes, French fries, hash browns, potato salad 1-3 times a week instead of 4 or more times a week.  2|144 144
Choose fried potatoes, French fries, hash browns, potato salad 1-3 times a week instead of 4 or more times a week.  2|182 182
Choose fried potatoes, French fries, hash browns, potato salad 1-3 times a week instead of 4 or more times a week.  2|219 219
Choose fried potatoes, French fries, hash browns, potato salad 1-3 times a week instead of 4 or more times a week.  2|256 256
Choose fried potatoes, French fries, hash browns, potato salad 1-3 times a week instead of 4 or more times a week.  2|293 293
Choose fried potatoes, French fries, hash browns, potato salad 1-3 times a week instead of 4 or more times a week.  2|330 330
Choose canned fruit, in heavy syrup, 1-3 times a week instead of 4 or more times a week.    3|331 331
Choose canned fruit, in heavy syrup, 1-3 times a week instead of 4 or more times a week.    3|294 294
Choose canned fruit, in heavy syrup, 1-3 times a week instead of 4 or more times a week.    3|257 257
Choose canned fruit, in heavy syrup, 1-3 times a week instead of 4 or more times a week.    3|220 220
Choose canned fruit, in heavy syrup, 1-3 times a week instead of 4 or more times a week.    3|183 183
Choose canned fruit, in heavy syrup, 1-3 times a week instead of 4 or more times a week.    3|145 145
Choose canned fruit, in heavy syrup, 1-3 times a week instead of 4 or more times a week.    3|107 107
Choose canned fruit, in heavy syrup, 1-3 times a week instead of 4 or more times a week.    3|3 3
Choose lower fat milk (nonfat, skim, 1%, 2%) instead of whole milk. 4|4 4
Choose lower fat milk (nonfat, skim, 1%, 2%) instead of whole milk. 4|108 108
Choose lower fat milk (nonfat, skim, 1%, 2%) instead of whole milk. 4|146 146
Choose lower fat milk (nonfat, skim, 1%, 2%) instead of whole milk. 4|184 184
Choose lower fat milk (nonfat, skim, 1%, 2%) instead of whole milk. 4|221 221

Select GoalReqID
  from GoalRequirement r inner join SurveyAnswer a
   on r.QuestionID = a.QuestionID and  ReqQuestionValue = SurveyAnswer 
   where a.CycleID = 93 and ReqBMILevel is null
  and ReqEnergyBalance is null and SurveyAnswer = 1

Results:

GoalReqID
478
479
480
481
482
440
441
483
484
485
442
443
486
487
444
488
489
445

Select  QuestionID from Question where QuestionParent = 6000

Results:

QuestionID
6000
6001
6002
6003
6004
6005
6006
6007
6008
6009

Answer 1

You're joining two tables together. Obviously, records in the first table match more than one record from the 2nd table. When that happens, the record from the first table is duplicated in the result set for every record in the 2nd table that it matches.

Answer 2

When a row in the first table joins to more than one row in the 2nd table, you apparently only want it to show once in the query output. In that case, WHICH row from the 2nd table do you want to show in this single output row??

• The row entered last?
• with biggest goalId?
• or whatever...

You need to answer this question before you can write a SQL query to do whatever your answer specifies..

Answer 3

Re-wrote your query so it's more readable, and turns subquerys (yuck) into JOINs:

SELECT g.goaltext
       g.goalid, 
       gr.goalreqid
  FROM GOAL g
  JOIN GOALREQUIREMENT gr ON gr.goalid = g.goalid AND gr.reqbmilevel IS NULL AND gr.reqenergybalance IS NULL
  JOIN JOIN SURVEYANSWER sa ON sa.questionid = gr.questionid AND sa.surveyanswer = gr.reqquestionvalue AND sa.surveyanswer = 1
  JOIN QUESTION q ON q.questionid = gr.questionid
 WHERE sa.cycleid = 93

It's the goalid and/or goalreqid column(s) that causing the rows to come out duplicated. Because the goaltext column is associated to multiple goalid/etcs, you're never going to get single entries for goaltext while including goalids.