Group using data from one query into another-CodePudding

I have a table that looks like below. It is created using a query -

NPI   Other_Columns
123   Several_Other_Columns
456   Several_Other_Columns

How do I take every NPI from this table and get a count of the number of times they appeared in another table? The structure of the other table is like so -

Claim_id  NPI1  NPI2  NPI3  NPI4  NPI5  NPI6  NPI7  NPI8

If NPIs in the first table, show in any field in the second table, we want to count that claim.s

CodePudding user response：

The first task is the join

SELECT
   t1.npi,
   t1.other_columns,
   t2.claim_id
FROM table1 as t1
JOIN table2 as t2 ON t1.npi in (t2.np1,t2.np2,t2.np3,t2.np4,t2.np5,t2.np6,t2.np7,t2.np8)

that gets you all the things joined.

Now count those..

SELECT
   count(t2.claim_id)
FROM table1 as t1
JOIN table2 as t2 ON t1.npi in (t2.np1,t2.np2,t2.np3,t2.np4,t2.np5,t2.np6,t2.np7,t2.np8)