How to convert a query into a sub query to return all values

Question

i have the following query which gives me the correct result as follows:

SELECT *
FROM dbo.QAA
INNER JOIN dbo.QA
ON QAA.ID = QA.ID
INNER JOIN dbo.Q
ON Q.QID = QA.QID
INNER JOIN dbo.V
ON V.VID = QAA.VID
Where
Q.QID = 1111
and V.SID = 4785
and V.VID = QAA.VID
and QA.TypeID = 4 and code <> '75785'

which gives me back the following table

Answer       | Code | User 
Apples       |45879 |958-dfdf255-sdfsdf
Banana       |45214 |958-dfdf255-sdfsdf
Carrot       |74896 |958-dfdf255-sdfsdf

as you can see i get 3 different answers above. Now i needed to add this query as a subquery as follows

    SELECT Name 
         , FirstName 
,table.code
        (SELECT Top 1
                QAA.Answer
                FROM dbo.QAA
        INNER JOIN dbo.QA
        ON QAA.ID = QA.ID
        INNER JOIN dbo.Q
        ON Q.QID = QA.QID
        INNER JOIN dbo.V
        ON V.VID = QAA.VID
        Where
        Q.QID = 1111
        and V.SID = 4785
        and V.VID = QAA.VID
        and QA.TypeID = 4 and code <> '75785') AS AnswerComment 
    from table
    where table.code=5

but now i dont get all the answers back, this is how the table looks

 AnswerComment        | code | Name               | firstname
    Apples            |45879 |958-dfdf255-sdfsdf  | Jane
    Apples            |45214 |958-dfdf255-sdfsdf  |Jane
    Apples            |74896 |958-dfdf255-sdfsdf  |Jane

my question is in the subquery how do i return all of the answercomments?

I know Top 1 will only retrieve the first record its just an example to show. i also tried

select max (QAA.Answer)

but this also only returns one value back

Does this answer your question? [SQL join subquery](https://stackoverflow.com/questions/18718444/sql-join-subquery) — Preben Huybrechts, Jul 15 '20 at 10:21
`TOP` without an `ORDER BY` is always a bad idea. It means that SQL Server is free to return any arbitrary row from the dataset, which means that that could be different **everytime** you run the query. If a subquery is returning more than 1 row, and it shouldn't be, that means your implicit join is wrong. If it i*should* be returning more than 1 but you only want 1, then you need to ensure you put an `ORDER BY` in the sub query to ensure consistent and reliable results to return the row you need. — Thom A, Jul 15 '20 at 10:31
@Larnu but i want it to return all of the answers, cause now it will show the same answer for every code — josh, Jul 15 '20 at 10:34
If you want all the rows, then you likely aren't after a subquery in the `SELECT`, which can only return a **scalar** value. — Thom A, Jul 15 '20 at 10:35

score 0 · Answer 1 · answered Jul 15 '20 at 10:34

Use a join on your original query if you want to bring in the names. I am guessing the join key is on the user column:

SELECT ?.AnswerComment, ?.code, n.Name, n.firstname
FROM dbo.QAA INNER JOIN
     dbo.QA
     ON QAA.ID = QA.ID INNER JOIN
     dbo.Q
     ON Q.QID = QA.QID INNER JOIN
     dbo.V
     ON V.VID = QAA.VID JOIN
     names n -- your new table
     ON n.user = ?.user
WHERE Q.QID = 1111 AND
      V.SID = 4785 AND
      QA.TypeID = 4 AND
      ?.code <> '75785'

Note that I removed this condition V.VID = QAA.VID because it is redundant.

The ? are for table aliases to properly qualify column names.

How to convert a query into a sub query to return all values

1 Answers1