Keith McComb Keith McComb - 11 months ago 46
SQL Question

Sub-query after WHERE works when alone, but not as sub-query

USE [AdventureWorks2014]

FROM [Person].[Person] PERSON, [Sales].[SalesPerson] SP
FROM Sales.SalesOrderHeader) >
FROM Sales.SalesOrderHeader);

If I run this, I get the error:

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

However, if I remove everything between "GO" and the WHERE (also removing the first set of parentheses), the query runs fine, and gives me an answer of something over 4000. (Which sounds right to me.)

I am new to this, so this is really kicking my butt. I just know that I'm missing something really simple, too, but I just can NOT see it right now, and I've been fighting it for long enough that my brain has gone to mush. (Running SQL Server Express 2014, if that turns out to be important, by the way.)

EDIT: As per Ted's comments, let me rephrase the question, now that I'm significantly more awake, and also state that I am looking for GUIDANCE, and not for someone to write it for me, because I learn nothing if you guys do it for me.

I am using the AdventureWorks database supplied by Microsoft for SQL Server for so many years. I need to retrieve the first and last name from the Person.Person table, and all information from the Sales.Salesperson table. However, the only ones I want to display are the ones that have an amount in the Sales.SalesOrderHeader.TotalDue column that is GREATER than the average of the entire Sales.SalesOrderHeader.TotalDue column. The Person.Person and Sales.SalesPerson share the BusinessEntityID key, and TerritoryID is shared between Sales.SalesOrderHeader and Sales.SalesPerson. If I use a JOIN of any sort, it enters an endless loop that I have to stop manually.

If I comment out the outer query, the query returns an answer of 4012. But all attempts I have made since then to get that data to affect my outer join have either thrown various error messages or endless loops.

If more information is needed about this issue, please tell me - I am trying to learn both SQL and how to properly ask the questions. (Not pissing Ted off is a side benefit.)

Ted Ted
Answer Source

In the statement below:

(SELECT TotalDue FROM Sales.SalesOrderHeader) 
(SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader);

(SELECT TotalDue FROM Sales.SalesOrderHeader) can possibly return more than one row while (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader) will always return one.

You cannot compare 1 with many using the > operator.

Perhaps you mean to use max or min:

(SELECT MAX(TotalDue) FROM Sales.SalesOrderHeader) 
(SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader);