SQL QUERY "Subquery returned more than 1 value"

Question

I've tried modifying a SQL query from an application but cannot get it working

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."

the original query:

SELECT fycode, fyname, class3, '', '', '', defje, zjm, '', ''
FROM zy_fy

UNION ALL
SELECT Ypcode, Ypname,
    (SELECT a.lbname FROM YK_yplb a where a.lbid = b.yplb)
, gg, sldw, '', '', '', '', ''
FROM YK_ypzd b

UNION ALL
SELECT FYID, NAME, DYKS+'-'+CLASS2, '', '次', '', FYMONEY, ZJM, ZJM1, '' 
FROM mz_fy

the modified query:

SELECT fycode, fyname, class3, '', '', '', defje, zjm, '', ''
FROM zy_fy

UNION ALL
SELECT Ypcode, Ypname, 
(select a.lbname from YK_yplb a  where a.lbid = b.yplb)
,gg,sldw, '', 
(select c.dj from YK_kc c  where c.Ypcode = b.Ypcode)
 ,'','',''
FROM YK_ypzd b

UNION ALL
SELECT FYID, NAME, DYKS+'-'+CLASS2, '', '次', '', FYMONEY, ZJM, ZJM1, ''
FROM mz_fy

I've just inserted a subquery in the 7th column of the 3rd select statement

Thank you!

Answer 1

You just need to use TOP 1 to limit the subquery to return only one row

SELECT fycode, fyname, class3, '', '', '', defje, zjm, '', ''
FROM zy_fy

UNION ALL
SELECT Ypcode, Ypname, 
(select TOP 1 a.lbname from YK_yplb a  where a.lbid = b.yplb)
,gg,sldw, '', 
(select TOP 1 c.dj from YK_kc c  where c.Ypcode = b.Ypcode)
 ,'','',''
FROM YK_ypzd b

UNION ALL
SELECT FYID, NAME, DYKS+'-'+CLASS2, '', '次', '', FYMONEY, ZJM, ZJM1, ''
FROM mz_fy