0

I have this big query:

SELECT C.CODDIPBILDIP                                       AS CODDIPBIDIRE,
       Isnull(A.NOMEDIPENDEN, '') + ' '
       + Isnull(A.COGNOMDIPEND, '')                         AS DIPENDENTERE,
       Sum(C.SALDBILADIPE)                                  AS NUOVAESPRESS,
       Isnull((SELECT E.DESCRICOMMES
               FROM   ORECOMMESSE D,
                      COMMESSE E
               WHERE  E.CODICECOMMES = D.CODCOMORECOM
                      AND ( D.CODDIPORECOM = C.CODDIPBILDIP )
                      AND ( D.MESEORECOMME = 'Settembre' )
                      AND ( D.ANNOORECOMME = 2016 )), '')   AS COMMESSA,
       Isnull((SELECT F.SALDOBILANC
               FROM   ORECOMMESSE F
               WHERE  ( F.CODDIPORECOM = C.CODDIPBILDIP )
                      AND ( F.MESEORECOMME = 'Settembre' )
                      AND ( F.ANNOORECOMME = 2016 )), NULL) AS SCORPORAZION
FROM   DIPENDENTI A,
       BILANCDIPEND C
WHERE  A.CODICEDIPEND = C.CODDIPBILDIP
       AND ( C.MESEBILADIPE = 'Settembre' )
       AND ( C.ANNOBILADIPE = 2016 )
       AND ( C.CODCONECBIDI NOT IN ( '56102', '56105', '56106' ) )
       AND ( A.CODICEDIPEND NOT IN (SELECT DISTINCT B.CODDIPOREDIP
                                    FROM   ORETOTALDIPE B
                                    WHERE  ( B.MESEOREDIPEN = 'Settembre' )
                                           AND ( B.ANNOOREDIPEN = 2016 )) )
GROUP  BY C.CODDIPBILDIP,
          Isnull(A.NOMEDIPENDEN, '') + ' '
          + Isnull(A.COGNOMDIPEND, '')

which I would need to re-arrange so that to avoid this error

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

caused when the two subqueries in the main select return more than one result. I'm not very good at handling these big queries so how can I fix it? I've tried to LEFT JOIN table ORECOMMESSE with the others but I always get null. I tried with left join because data in BILANCDIPEND might not be in ORECOMMESSE but I want to get tem even if there's no join that's why I tried with left join...but it does not work. That's why I thought about that more complex query but it fails when the subqueries return more than one record... Thank you

UPDATE

Thanks to Jerry Ritcey's suggestion I was able to write the correct query which is:

select
  D.COGNOMDIPEND as COGNDIPERECO,
  SUM(B.SALDBILADIPE) as BILANCINO,
  A.SALDOBILANC as SALORECOMREC,
  E.DESCRICOMMES as DESCCOMMRECO,
  ISNULL(D.NOMEDIPENDEN, '') + '  ' + ISNULL(D.COGNOMDIPEND, '') as NOMEDIPERECO
from
  (((BILANCDIPEND B  left join  DIPENDENTI D on D.CODICEDIPEND =  B.CODDIPBILDIP and (D.CODICEDIPEND = B.CODDIPBILDIP))
  left join  ORECOMMESSE A on (A.CODDIPORECOM = B.CODDIPBILDIP))
  left join  COMMESSE E on E.CODICECOMMES = A.CODCOMORECOM)
where (B.MESEBILADIPE = 'September')
      and   (B.ANNOBILADIPE = 2016)
      and   (B.CODCONECBIDI NOT IN ('56102','56105','56106'))
      and   (B.CODDIPBILDIP NOT IN (
  select
    C.CODDIPOREDIP
  from
    ORETOTALDIPE C
  where (C.MESEOREDIPEN = 'September')
        and   (C.ANNOOREDIPEN = 2016)))
group by
  A.SALDOBILANC,
  E.DESCRICOMMES,
  ISNULL(D.NOMEDIPENDEN, '') + '  ' + ISNULL(D.COGNOMDIPEND, ''),
  D.COGNOMDIPEND
SagittariusA
  • 5,289
  • 15
  • 73
  • 127
  • 1
    Use Top 1 in the inner selects. Also, you should join your tables on the outer select, do not just select from DIPENDENTI A, BILANCDIPEND C. – SS_DBA Oct 27 '16 at 16:46
  • I cannot user `TOP 1` in the inner selects because I need all those results and not only the first... – SagittariusA Oct 27 '16 at 16:49
  • How are these tables related with outer query `COMMESSE,ORECOMMESSE` – Pரதீப் Oct 27 '16 at 16:53
  • 1
    If you need all of the results from the inner selects, then you can't have them as inner selects. Put the tables into the main query as Left Joins – SS_DBA Oct 27 '16 at 16:55
  • @WEI_DBA: I'm trying to do it but I must be inserting too many where clauses because I get no result. – SagittariusA Oct 27 '16 at 16:58
  • @Prdp: `COMMESSE` contains ID and DESCRIPTION of clients while `ORECOMMESSE` contains info declaring which employee worked for which client and how many hours. So there's a FK in `ORECOMMESSE` to `COMMESSE` – SagittariusA Oct 27 '16 at 16:59
  • I was asking about how it is related with outer query.. (ie) How it is related with `DIPENDENTI` or `BILANCDIPEND` table ? – Pரதீப் Oct 27 '16 at 17:01
  • 1
    Just join to them... left outer join (SELECT E.DESCRICOMMES FROM ORECOMMESSE D, COMMESSE E WHERE E.CODICECOMMES = D.CODCOMORECOM AND ( D.MESEORECOMME = 'Settembre' ) AND ( D.ANNOORECOMME = 2016 )) as DE on ( D.CODDIPORECOM = C.CODDIPBILDIP ) LEFT OUTER JOIN ( SELECT F.SALDOBILANC FROM ORECOMMESSE F WHERE ( F.MESEORECOMME = 'Settembre' ) AND ( F.ANNOORECOMME = 2016 ) ) as F on F.CODDIPORECOM = C.CODDIPBILDIP – Jerry Ritcey Oct 27 '16 at 17:21

0 Answers0