using Query one results to run Query two

Question


    SELECT distinct
    Pd.Cpd as ' accountnumber'
    FROM [RQL_ALK_PMT].[Cts_opps] pd
    INNER JOIN [RQL_ALK_PMT].[Cts_opps].dpo.cnms_id metg on metg.cnms_id=me.cnms_id

This code would result with this

Result

accountnumber  
1332132
3213123
5641202
6412221
1233242

What I would like to do is when the code above gets the results my bottom code reads them and runs them trough its code. The common denominator here would be the account number because its running through a different table

 SELECT 
    pm.AcctNumb as 'accountnumber'
    , SUM(CASE WHEN pm.cusidIN ('cr') THEN 1 ELSE 0 END) AS CA
    , SUM(CASE WHEN pm.cusidIN ('gb') THEN 1 ELSE 0 END) AS GB
    , SUM(CASE WHEN pm.cusidIN ('tev','offev','Lastev') THEN 1 ELSE 0 END) AS chr
    , SUM(CASE WHEN pm.cusidIN 
    ('pm','pr','che' )
    THEN 1 ELSE 0 END) AS Act
    , SUM(CASE WHEN pm.cusidIN ('supev','tev') THEN 1 ELSE 0 END) AS Fulfillment
 FROM ops.medadata pm WITH (NOLOCK)
  INNER JOIN mw.pim_acct Ma with (nolock) ON ma.AcctNumb= pm.AcctNumb
Where pm.AcctNumb in ()
  GROUP BY 
 pm.AcctNumb

I have tried doing this the code below but it doesnt seem to work

With counta as (

SELECT distinct
Pd.Cpd as ' accountnumber'
FROM [RQL_ALK_PMT].[Cts_opps] pd
INNER JOIN [RQL_ALK_PMT].[Cts_opps].dpo.cnms_id metg on metg.cnms_id=me.cnms_id
)
SELECT 
pm.AcctNumb as 'accountnumber'
, SUM(CASE WHEN pm.cusidIN ('cr') THEN 1 ELSE 0 END) AS CA
, SUM(CASE WHEN pm.cusidIN ('gb') THEN 1 ELSE 0 END) AS GB
, SUM(CASE WHEN pm.cusidIN ('tev','offev','Lastev') THEN 1 ELSE 0 END) AS chr
, SUM(CASE WHEN pm.cusidIN 
('pm','pr','che' )
THEN 1 ELSE 0 END) AS Act
, SUM(CASE WHEN pm.cusidIN ('supev','tev') THEN 1 ELSE 0 END) AS Fulfillment
 FROM ops.medadata pm WITH (NOLOCK)
  INNER JOIN mw.pim_acct Ma with (nolock) ON ma.AcctNumb= pm.AcctNumb
left join counta on Pm.accountnumber = counta.accountnumber
Where pm.AcctNumb in (counta.accountnumber)
  GROUP BY 
 pm.AcctNumb

**im having issue with joining  the two tables together**

In some places, you refer to `pm.AcctNumb` but in your `join` condition you use `Pm.accountnumber`. Are these two different fields or is that the problem you are having? — EdmCoff, Jun 24 '22 at 22:18
"it doesnt seem to work" means very little. Do you get an error? Or unexpected results? Tell us the problem — HoneyBadger, Jun 24 '22 at 22:36
`as ' accountnumber'` I wouldn't include spaces in that alias, nor would I use single quotes when declaring it — Paul Maxwell, Jun 25 '22 at 00:24

Paul Maxwell · Accepted Answer · 2022-06-25T00:20:39.730

IN is not an equivalent to a join - but you are treating that way.

Instead think of "IN this LIST" and the list could be supplied by you, or by a subquery e.g.

a list given by the query itself

select * from atable
where acol IN ('a','b','c') -- i.e. the list is hardcoded

or, using a subquery

SELECT ...etc.
WHERE pm.AcctNumb IN (
    SELECT Pd.Cpd
    FROM [RQL_ALK_PMT].[Cts_opps] pd
    INNER JOIN [RQL_ALK_PMT].[Cts_opps].dpo.cnms_id metg on metg.cnms_id=me.cnms_id
    )

or, if using a CTE

With counta as (
    SELECT Pd.Cpd
    FROM [RQL_ALK_PMT].[Cts_opps] pd
    INNER JOIN [RQL_ALK_PMT].[Cts_opps].dpo.cnms_id metg on metg.cnms_id=me.cnms_id
    )
SELECT ... etc.
WHERE pm.AcctNumb IN (
    SELECT Cpd
    FROM counta
    )

note, it usually is not more efficient to use select distinct when forming a subquery to be used with an IN list.

using Query one results to run Query two

1 Answers1