SQL Server 2005 returns error when executing a query with a select query as join

Question

I receive the syntax error message when I try to execute the code shown below

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'join'.
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'order'.

But when I execute the 2nd query without the brackets () the results are ok. So when I execute the query in a new query window with the brackets in place, I receive this message. A query must be able to run within brackets ?!? Thanks for reading (and hopefully answering XD) my question.

I tried to add spaces for readability, I hope it works.

SELECT 
   faknr, tot_bdr as verkooporderbedrag
FROM 
   [001].[dbo].[frhkrg] faktuur
WHERE 
   dagbknr = 70 
JOIN
    (SELECT 
        SUM(amutas.bedrag) AS totaalbedrag_regels,
        amutak.bkstnr, amutas.reknr, amutas.faktuurnr AS faknr
     FROM 
        [001].[dbo].[amutak] 
     INNER JOIN
        [amutas] ON amutak.bkstnr = amutas.bkstnr
     WHERE 
        amutak.dagbknr = 90 
        AND status NOT IN ('V', 'O') 
        AND amutas.reknr = 1160
     GROUP BY 
        amutak.bkstnr, amutak.bkstnr, amutas.reknr, amutas.faktuurnr
     ORDER BY 
        amutak.bkstnr DESC) memoriaal ON faktuur.faknr = memoriaal.faknr

I've changed the query to the following:

SELECT 
   faktuur.faknr, tot_bdr AS verkooporderbedrag, SUM(totaalbedrag_regels) AS Totaalbedrag_verdeling
FROM [001].[dbo].[frhkrg] faktuur
    JOIN 
        (SELECT 
            SUM(amutas.bedrag) AS totaalbedrag_regels,amutak.bkstnr,amutas.reknr,amutas.faktuurnr AS faknr
         FROM 
            [001].[dbo].[amutak] INNER JOIN [amutas] ON amutak.bkstnr = amutas.bkstnr
         WHERE 
            amutak.dagbknr = 90 AND status NOT IN ('V', 'O') AND amutas.reknr = 1161
         GROUP BY 
            amutak.bkstnr,amutak.bkstnr,amutas.reknr,amutas.faktuurnr) memoriaal 
    ON faktuur.faknr = memoriaal.faknr
GROUP BY 
   faktuur.faknr,tot_bdr,totaalbedrag_regels
ORDER BY 
   faknr

Although my last GROUP BY statement, he still doesn't SUM (and GROUP) the results correctly. Several records are still separate:

faknr           verkooporderbedrag  Totaalbedrag_verdeling
14700218            5115                    4880,05
14700218            5115                    234,95

The data type of "Totaalbedrag_verdeling" is a number (I can do mathematical actions with it) and the other two values are the same... Someone has an update?

/ME STUPID: A column that must be SUM (or MAX etc) may not be included in the GROUP BY statement ....

Use [Cross Apply](http://technet.microsoft.com/en-us/library/ms175156%28v=sql.105%29.aspx) — huMpty duMpty, Apr 29 '14 at 11:43
Didn't understand the link very well.. When i have to apply "Cross Apply" instead of a join. But i will check it later on. Thanks for the feedback !! — Peter O, Apr 29 '14 at 12:20
Here you go [When should I use Cross Apply over Inner Join?](http://stackoverflow.com/questions/1139160/when-should-i-use-cross-apply-over-inner-join) — huMpty duMpty, Apr 29 '14 at 12:22
Again... Thanks for the quick reply !! I will check this article. Now i need to edit my query because it didn't showed me the correct result that i wanted :) — Peter O, Apr 29 '14 at 12:32

score 2 · Accepted Answer · answered Apr 29 '14 at 11:40

The where clause comes after the from clause:

SELECT faknr, tot_bdr as verkooporderbedrag
FROM [001].[dbo].[frhkrg] faktuur
        join 
    (SELECT SUM(amutas.bedrag) as totaalbedrag_regels,amutak.bkstnr,amutas.reknr,amutas.faktuurnr as faknr
     FROM [001].[dbo].[amutak] inner join [amutas] on amutak.bkstnr = amutas.bkstnr
     WHERE amutak.dagbknr = 90 and status not in ('V', 'O') and amutas.reknr = 1160
     GROUP BY amutak.bkstnr,amutak.bkstnr,amutas.reknr,amutas.faktuurnr
    ) 
    memoriaal on faktuur.faknr = memoriaal.faknr
where faktuurdagbknr = 70;

Also, the order by in the subquery is superfluous.

score 0 · Answer 2 · answered Apr 29 '14 at 11:41

Move where clause after the JOIN ON clause and move ORDER BY clause outside subquery

SELECT faknr, tot_bdr as verkooporderbedrag
          FROM [001].[dbo].[frhkrg] faktuur
        join 
    (SELECT SUM(amutas.bedrag) as totaalbedrag_regels,amutak.bkstnr,amutas.reknr,amutas.faktuurnr as faknr
    FROM [001].[dbo].[amutak] inner join [amutas] on amutak.bkstnr = amutas.bkstnr
    WHERE amutak.dagbknr = 90 and status not in ('V', 'O') and amutas.reknr = 1160
    GROUP BY amutak.bkstnr,amutak.bkstnr,amutas.reknr,amutas.faktuurnr
    ) 
    memoriaal on faktuur.faknr = memoriaal.faknr
    where dagbknr = 70 
    ORDER BY amutak.bkstnr desc

score 0 · Answer 3 · answered Apr 29 '14 at 11:45

   SELECT faknr, tot_bdr as verkooporderbedrag
                  FROM [001].[dbo].[frhkrg] faktuur 
    join (SELECT SUM(amutas.bedrag) as 
totaalbedrag_regels,amutak.bkstnr,amutas.reknr,amutas.faktuurnr as faknr
                FROM [001].[dbo].[amutak] inner join [amutas] on amutak.bkstnr = amutas.bkstnr
                WHERE amutak.dagbknr = 90 and status not in ('V', 'O') and amutas.reknr = 1160
                GROUP BY amutak.bkstnr,amutak.bkstnr,amutas.reknr,amutas.faktuurnr
                ORDER BY amutak.bkstnr desc) 
                memoriaal on faktuur.faknr = memoriaal.faknr
    where dagbknr = 70

SQL Server 2005 returns error when executing a query with a select query as join

3 Answers3