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

Question

Post reply

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

Tom-173069

Valued Member

Points: 73
More actions
August 26, 2014 at 6:48 pm

#295764

SELECT ValveSections.PipeSystemID, ValveSections.PipelineID, ValveSections.OrionStationSeries, ValveSections.ValveSectionBegin,
(SELECT (COALESCE(FirstName, '') + ' ' + COALESCE(LastName,''))
FROM Users INNER JOIN
ValveSections ON Users.UserID = ValveSections.BuilderID) AS Builder,
(SELECT (COALESCE(FirstName, '') + ' ' + COALESCE(LastName,''))
FROM Users INNER JOIN
ValveSections ON Users.UserID = ValveSections.QCID) AS QC,
(SELECT (COALESCE(FirstName, '') + ' ' + COALESCE(LastName,''))
FROM Users INNER JOIN
ValveSections ON Users.UserID = ValveSections.EngineerID) AS Engineer,
(SELECT (COALESCE(FirstName, '') + ' ' + COALESCE(LastName,''))
FROM Users INNER JOIN
ValveSections ON Users.UserID = ValveSections.FinalEngineerID) AS 'Final Engineer',ValveSectionStatus.ValveSectionStatusItem
FROM ValveSections INNER JOIN
ValveSectionStatus ON ValveSections.ValveSectionStatusID = ValveSectionStatus.ValveSectionStatusID INNER JOIN
Users ON ValveSections.QCID = Users.UserID

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Here's your query reformatted to make it easier to understand what you are trying to do:

SELECT

ValveSections.PipeSystemID,

ValveSections.PipelineID,

ValveSections.OrionStationSeries,

ValveSections.ValveSectionBegin,

Builder = (SELECT (COALESCE(FirstName, '') + ' ' + COALESCE(LastName,''))

FROM Users

INNER JOIN ValveSections ON Users.UserID = ValveSections.BuilderID),

QC = (SELECT (COALESCE(FirstName, '') + ' ' + COALESCE(LastName,''))

FROM Users

INNER JOIN ValveSections ON Users.UserID = ValveSections.QCID),

Engineer = (SELECT (COALESCE(FirstName, '') + ' ' + COALESCE(LastName,''))

FROM Users

INNER JOIN ValveSections ON Users.UserID = ValveSections.EngineerID),

[Final Engineer] = (SELECT (COALESCE(FirstName, '') + ' ' + COALESCE(LastName,''))

FROM Users

INNER JOIN ValveSections ON Users.UserID = ValveSections.FinalEngineerID),

ValveSectionStatus.ValveSectionStatusItem

FROM ValveSections

INNER JOIN ValveSectionStatus

ON ValveSections.ValveSectionStatusID = ValveSectionStatus.ValveSectionStatusID

INNER JOIN Users

ON ValveSections.QCID = Users.UserID

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 2

Further on Chris's post, can you provide DDL (create table script) and some sample data?

😎

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Here's one of those subqueries from the output list. Run it and see what it returns.

SELECT [Final Engineer] = (COALESCE(FirstName, '') + ' ' + COALESCE(LastName,''))

FROM Users

INNER JOIN ValveSections ON Users.UserID = vs.FinalEngineerID

You probably don't want all rows returned - you forgot to correlate the subquery with the main query. Here's how it's done:

SELECT

vs.PipeSystemID,

vs.PipelineID,

vs.OrionStationSeries,

vs.ValveSectionBegin,

Builder = (SELECT (COALESCE(FirstName, '') + ' ' + COALESCE(LastName,''))

FROM Users

WHERE Users.UserID = vs.BuilderID),

QC = (SELECT (COALESCE(FirstName, '') + ' ' + COALESCE(LastName,''))

FROM Users

WHERE Users.UserID = vs.QCID),

Engineer = (SELECT (COALESCE(FirstName, '') + ' ' + COALESCE(LastName,''))

FROM Users

WHERE Users.UserID = vs.EngineerID),

[Final Engineer] = (SELECT (COALESCE(FirstName, '') + ' ' + COALESCE(LastName,''))

FROM Users

WHERE Users.UserID = vs.FinalEngineerID),

vss.ValveSectionStatusItem

FROM ValveSections vs

INNER JOIN ValveSectionStatus vss

ON vs.ValveSectionStatusID = vss.ValveSectionStatusID

Notice the use of aliases to distinguish tables. I've also removed "INNER JOIN Users" from the FROM list - it's not required.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

In this version, I've made each table reference completely unambiguous and also included TOP to eliminate dupes in the subqueries. This is one of those very few cases where TOP without ORDER BY is sensible.

SELECT

vs.PipeSystemID,

vs.PipelineID,

vs.OrionStationSeries,

vs.ValveSectionBegin,

Builder = (SELECT TOP 1 ISNULL(u1.FirstName + ' ', '') + ISNULL(u1.LastName,'')

FROM Users u1

WHERE u1.UserID = vs.BuilderID),

QC = (SELECT TOP 1 ISNULL(u2.FirstName + ' ', '') + ISNULL(u2.LastName,'')

FROM Users u2

WHERE u2.UserID = vs.QCID),

Engineer = (SELECT TOP 1 ISNULL(u3.FirstName + ' ', '') + ISNULL(u3.LastName,'')

FROM Users u3

WHERE u3.UserID = vs.EngineerID),

[Final Engineer] = (SELECT TOP 1 ISNULL(u4.FirstName + ' ', '') + ISNULL(u4.LastName,'')

FROM Users u4

WHERE u4.UserID = vs.FinalEngineerID),

vss.ValveSectionStatusItem

FROM ValveSections vs

INNER JOIN ValveSectionStatus vss

ON vs.ValveSectionStatusID = vss.ValveSectionStatusID

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Finally, here's one of several alternative ways to write essentially the same query:

SELECT

vs.PipeSystemID,

vs.PipelineID,

vs.OrionStationSeries,

vs.ValveSectionBegin,

Builder = ISNULL(u1.FirstName + ' ', '') + ISNULL(u1.LastName,''),

QC = ISNULL(u2.FirstName + ' ', '') + ISNULL(u2.LastName,''),

Engineer = ISNULL(u3.FirstName + ' ', '') + ISNULL(u3.LastName,''),

[Final Engineer] = ISNULL(u4.FirstName + ' ', '') + ISNULL(u4.LastName,''),

vss.ValveSectionStatusItem

FROM ValveSections vs

INNER JOIN ValveSectionStatus vss

ON vs.ValveSectionStatusID = vss.ValveSectionStatusID

LEFT JOIN Users u1

ON u1.UserID = vs.BuilderID

LEFT JOIN Users u2

ON u2.UserID = vs.QCID

LEFT JOIN Users u3

ON u3.UserID = vs.EngineerID

LEFT JOIN Users u4

ON u4.UserID = vs.FinalEngineerID

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Umer Akram SSC-Insane Points: 22209 More actions · Answer 6

i would recommend to check you Users table 1st. i feel there are duplicates and that might be give you the clue

Following is the example have been made to illustrate the issue:

------------ Data before duplicate

declare @Users table ( UserID smallint, UsersName varchar(20))

declare @ValveSections table ( ValveSectionsID smallint, BuilderID smallint, QCID Smallint)

insert into @Users

select 1, 'Eng01' union all

select 2, 'Eng02' union all

select 3, 'Eng03' union all

select 4, 'Eng04'

insert into @ValveSections

select 1,1,4 union all

select 2,2,2 union all

select 3,3,3 union all

select 4,1,4

SELECT

vs.ValveSectionsID,

Builder = (SELECT UsersName

FROM @Users Users

WHERE Users.UserID = vs.BuilderID),

QC = (SELECT UsersName

FROM @Users Users

WHERE Users.UserID = vs.QCID)

FROM @ValveSections vs

------------ Data After duplicates

insert into @Users

select 1, 'Eng012'

SELECT

vs.ValveSectionsID,

Builder = (SELECT UsersName

FROM @Users Users

WHERE Users.UserID = vs.BuilderID),

QC = (SELECT UsersName

FROM @Users Users

WHERE Users.UserID = vs.QCID)

FROM @ValveSections vs

Hope it helps.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

twin.devil (8/27/2014)
i would recommend to check you Users table 1st. i feel there are duplicates and that might be give you the clue
Following is the example have been made to illustrate the issue:
------------ Data before duplicate
declare @Users table ( UserID smallint, UsersName varchar(20))
declare @ValveSections table ( ValveSectionsID smallint, BuilderID smallint, QCID Smallint)
insert into @Users
select 1, 'Eng01' union all
select 2, 'Eng02' union all
select 3, 'Eng03' union all
select 4, 'Eng04'
insert into @ValveSections
select 1,1,4 union all
select 2,2,2 union all
select 3,3,3 union all
select 4,1,4
SELECT
vs.ValveSectionsID,
Builder = (SELECT UsersName
FROM @Users Users
WHERE Users.UserID = vs.BuilderID),
QC = (SELECT UsersName
FROM @Users Users
WHERE Users.UserID = vs.QCID)
FROM @ValveSections vs
------------ Data After duplicates
insert into @Users
select 1, 'Eng012'
SELECT
vs.ValveSectionsID,
Builder = (SELECT UsersName
FROM @Users Users
WHERE Users.UserID = vs.BuilderID),
QC = (SELECT UsersName
FROM @Users Users
WHERE Users.UserID = vs.QCID)
FROM @ValveSections vs
Hope it helps.

The most likely reason is because the subqueries in the output list weren't correlated to the tables in the FROM list.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden