Problem with simple subquery in SQL2005 AND SQL2000.

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 docSectionText

WHERE (docSections.SectionID = docSectionText.SectionID)

AND (docSectionText.colOrdinal = 1)) AS SecTitle

FROM docSections

Can anyone please let me know what I do wrong here.

Thanks

Gerhard

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

|||

Thank You very much.

You were correct. I had 2 doubles in my table.

Gerhard