I need a help with sql nested sub query please.

Clarifications

if you want to stick with the subquery
it's
where p.bedno IN (select...)

You are getting the error because you have more than one used bed and the logic of teh command says your outer query has to used all of them

you could also change it to a correlated subquery.

SELECT p.bedno, p.refno, p.surename
FROM patient p
WHERE p.bedno =
(SELECT bedno
FROM bed
WHERE bedstatus = 'USED' and bed.bedno = p.bedno);

assuming of course bed no is unique

A join would be much better though

SELECT p.bedno, p.refno, p.surename
FROM patient p bed b
WHERE p.bedno = b.bedno
and bedstatus = 'USED;

CREATE VIEW view1 AS
SELECT p.bedno, p.refno, p.surename
FROM patient p
WHERE p.bedno in
(SELECT bedno
FROM bed
WHERE bedstatus = 'USED');

Also try running the query before you create the view.

As ur subquery returns two rows,then ur checking those two rows with bedno column of patient table using '=', which checks only one value at a time.so use IN operator instead of '='. The IN operator will checks multiple values at a time.