JOIN and SELECT in SELECT issues-CodePudding

I have 3 tables in which there are about 70 columns combined.

What I am trying to do is select everything from 1 and 2 but then there are certain conditions to get first and last score and those are in third table.

SELECT 
    ID, NAME, TIME, ROUNDS
    (SELECT SCORE
     FROM TABLE2 TAB2
     JOIN TABLE3 TAB3 ON TAB2.ID = TAB3.ID
     WHERE PARAMETER1 = 9 AND PARAMETER2 = 21) AS FIRST,
    (SELECT SCORE
     FROM TABLE2 TAB2
     JOIN TABLE3 TAB3 ON TAB2.ID = TAB3.ID
     WHERE PARAMETER1 = 15 AND PARAMETER2 = 2) AS LAST
FROM 
    TABLE1 TAB1
JOIN 
    TABLE2 TAB2 ON TAB1.ID = TAB2.ID
GROUP BY 
    ID, NAME, TIME, ROUNDS

I get the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I also tried to do the joins at the end and I do get a result but it is wrong

SELECT 
    ID, NAME, TIME, ROUNDS
    (SELECT SCORE
     WHERE PARAMETER1 = 9 AND PARAMETER2 = 21) AS FIRST,
    (SELECT SCORE
     WHERE PARAMETER1 = 15 AND PARAMETER2 = 2) AS LAST
FROM 
    TABLE1 TAB1
JOIN 
    TABLE2 TAB2 ON TAB1.ID = TAB2.ID
JOIN 
    TABLE3 TAB3 ON TAB2.ID = TAB3.ID

I get incorrect data because I cannot group it since it wants me to group parameters as well

Msg 8120, Level 16, State 1, Line 46
Column 'PARAMETER1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I really don't know what I'm doing wrong so is there any way you guys can help ?

CodePudding user response：

As I mentioned in the comments, you need to correlate your subqueries, and I suspect that you think that all 3 references to TABLE2 TAB2 are the same instance of said object. This is a flawed understanding. Each time you reference an object it is a new "instance" of it.

As such what you likely want is this:

SELECT TAB1.ID,
       TAB1.NAME,
       TAB2.TIME,
       TAB2.ROUNDS,
       (SELECT TAB3.SCORE
        FROM dbo.TABLE3 TAB3 
        WHERE TAB3.ID = TAB2.ID
          AND TAB3.PARAMETER1 = 9
          AND TAB3.PARAMETER2 = 21) AS FIRST,
       (SELECT TAB3.SCORE
        FROM dbo.TABLE3 TAB3 
        WHERE TAB3.ID = TAB2.ID
          AND TAB3.PARAMETER1 = 15
          AND TAB3.PARAMETER2 = 2) AS LAST
FROM dbo.TABLE1 TAB1
     JOIN dbo.TABLE2 TAB2 ON TAB1.ID = TAB2.ID;

Or perhaps you would be better off with conditional aggregation, which would mean 1 scan of the table dbo.TABLE3:

SELECT TAB1.ID,
       TAB1.NAME,
       TAB2.TIME,
       TAB2.ROUNDS,
       MAX(CASE WHEN TAB3.PARAMETER1 = 9 AND TAB3.PARAMETER2 = 21 THEN TAB3.Score END) AS FIRST,
       MAX(CASE WHEN TAB3.PARAMETER1 = 15 AND TAB3.PARAMETER2 = 2 THEN TAB3.Score END) AS FIRST
FROM dbo.TABLE1 TAB1
     JOIN dbo.TABLE2 TAB2 ON TAB1.ID = TAB2.ID
     JOIN dbo.TABLE3 TAB3 ON TAB2.ID = TAB3.ID
GROUP BY TAB1.ID,
         TAB1.NAME,
         TAB2.TIME,
         TAB2.ROUNDS;

Note that the tables the columns belong to is guessed. If incorrect, you'll need to correct.

CodePudding user response：

it seems below nested queries return multiple values .

    (SELECT SCORE
     WHERE PARAMETER1 = 9 AND PARAMETER2 = 21) AS FIRST
   (SELECT SCORE
     WHERE PARAMETER1 = 15 AND PARAMETER2 = 2) AS LAST

You should use top(1) with order by ascending for first and descending for last column as below.

SELECT 
    ID, NAME, TIME, ROUNDS
    (SELECT top(1) SCORE
     WHERE PARAMETER1 = 9 AND PARAMETER2 = 21 order by SCORE asc ) AS FIRST,
    (SELECT top(1) SCORE
     WHERE PARAMETER1 = 15 AND PARAMETER2 = 2 order by SCORE desc ) AS LAST
FROM 
    TABLE1 TAB1
JOIN 
    TABLE2 TAB2 ON TAB1.ID = TAB2.ID
JOIN 
    TABLE3 TAB3 ON TAB2.ID = TAB3.ID

I think above query helps you finding error on exact location.