When I use the simple query with a subquery shown below, this is the error message I get in SQL 2000 AND SQL 2005
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
And here is the query I use:
SELECT
docSections.SectionID,(
SELECT docSectionText.colText FROM docSectionTextWHERE
(docSections.SectionID = docSectionText.SectionID)AND
(docSectionText.colOrdinal = 1)) AS SecTitleFROM
docSections
Can anyone please let me know what I do wrong here.
Thanks
Gerhard
Problem with simple subquery in SQL2005 AND SQL2000.
stevensrf
I can tell you why you get the error. But, without understanding your schema and requirements, I can not give you a solution for what you are trying to do.
The problem is that when you have a subquery in your SELECT it can only return 1 row per row. So, your subquery must be returning multiple rows.
To check try the following queries and see what it returns.
-- This should show you the sectionid that have multiple rows with colOrdinal = 1
SELECT SECTIONID, count(coltext) as rowcount
FROM docSections
WHERE docSectionText.colOrdinal = 1
GROUP BY SectionID
HAVING count(coltext) > 1
-- This should checks if perhaps the identified sections have multiple rows
--but all with same coltext. If first returns rows, but this doesn't,
--then you can add distinct to solve your problems
SELECT SECTIONID, count(distinct coltext) as rowcount
FROM docSections
WHERE docSectionText.colOrdinal = 1
GROUP BY SectionID
HAVING count(distinct coltext) > 1
HTH
amisner2k
Thank You very much.
You were correct. I had 2 doubles in my table.
Gerhard