i need a query which returns 1 column 5 rows and 2nd column 10 rows

here is my query

SELECT 
ISNULL(
     (
     select subjectshrt from subject_definition sd  join student_subject ss
     on(sd.sdsrno =ss.sdsrno1) where sd.tpbp LIKE ('p%')
      )
,0)A,
ISNULL(
     (
     select subjectshrt from subject_definition sd  join student_subject ss
     on(sd.sdsrno =ss.sdsrno2) where sd.t开发者_如何转开发pbp LIKE ('p%')
      )
,0)b

here first select query return 1 row and 2nd returns multiple rows and am getting a error like

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

so what should i do to make this query work

You can use this instead

select
    case when exists(
        select subjectshrt from subject_definition sd  join student_subject ss
        on (sd.sdsrno = ss.sdsrno1) where sd.tpbp LIKE ('p%')
    ) then 1 else 0 end as A,
    case when exists(
        select subjectshrt from subject_definition sd  join student_subject ss
        on (sd.sdsrno = ss.sdsrno2) where sd.tpbp LIKE ('p%')
    ) then 1 else 0 end as B

but it would probably be much better to write a select statement with joins and get results as appropriate.

select case when sh.sdrno = ss.sdrno1 then sd.subjectshrt else 0 end as A, ... -- similar for B
from subject_definition sd
    left join student_subject ss
    on ((sh.sdsrno = ss.sdrno1) or (sh.sdsrno = ss.sdrno2))
where sd.tpbp like 'p%'

Checking execution plans for comparison reasons strongly advised.

Can't you modify the code to use one query and check ON (sd.sdsrno IN (ss.sdsrno1,ss.sdsrno2))? it seems like that is the only change here.

(Or maybe I'm missing the end result here. if so, please feel free to post a couple of example outputs you'd like to see)

SELECT  COALESCE(subjectshirt,0) AS subjectshirt
FROM    subject_definition
  JOIN  student_subject
  ON    (sd.sdsrno IN (ss.sdsrno1,ss.sdsrno2))
WHERE sd.tpbp LIKE 'p%'

Or "SELECT TOP 2" is preferred.

try using something like

SELECT isnull(sd1.subjectshrt,0) as A , isnull(sd2.subjectshrt,0) as B
from student_subject ss left join subject_definition sd1 on sd1.sdsrno =ss.sdsrno1,
left join subject_definition sd2 on sd2.sdsrno =ss.sdsrno2