TSQL subquery

The following query gives me what I want - invoices which have a non-zero balance as of a certain date.

SELECT [EPCONO]
      ,[EPDIVI]
      ,[EPSPYN]
      ,[EPSUNO]
      ,[EPSINO]
      ,[EPINYR]
      ,sum([EPCUAM]) as Balance

  FROM [MVXJDTA].[FPLEDGJ1]
 
WHERE EPACDT <201200816
 
GROUP BY EPCONO, EPDIVI, EPSPYN, EPSUNO, EPSINO, EPINYR

HAVING (sum([EPCUAM]))<>0

When I include this as a subquery in a query in order to get the detail records making up these sums, I get all records, not just those associated with the selected non-zero balance invoices.
gofasteddieAsked:
Who is Participating?
 
CluskittCommented:
Not knowing what your keys are, I'll have to assume something like:

SELECT *
FROM [MVXJDTA].[FPLEDGJ1]
INNER JOIN (SELECT [EPCONO] cono
      ,[EPDIVI] divi
      ,[EPSPYN] spyn
      ,[EPSUNO] suno
      ,[EPSINO] sino
      ,[EPINYR] inyr
      ,sum([EPCUAM]) as Balance
  FROM [MVXJDTA].[FPLEDGJ1]
WHERE EPACDT <201200816
GROUP BY EPCONO, EPDIVI, EPSPYN, EPSUNO, EPSINO, EPINYR
HAVING (sum([EPCUAM]))<>0) t
ON [EPCONO]=cono
      AND [EPDIVI]=divi
      AND [EPSPYN]=spyn
      AND [EPSUNO]=suno
      AND [EPSINO]=sino
      AND [EPINYR]=inyr
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.