# MySQL Count(Table1.ColId) Where IN (any of Table2.Col1 ,Table2.Col2)

I have 2 Tables

``````Table 1 has Id, Name
Table 2 has Id, Col1_IdVal, Col2_IdVal,Col3_IdVal,Col4_IdVal
``````

I would like simple solution for

``````Count(T1.Id) WHERE T1.Id In (T2.Col1_IdVal,T2.Col2_IdVal,T2.Col3_IdVal,T2.Col4_IdVal)
``````

Something I have tried does not return correct result set , too few in the result :

``````SELECT COUNT(T1.Id) FROM Table1 As T1
LEFT JOIN Table2 AS T2_1 ON T2_1.T2Column_1 = T1.Id
LEFT JOIN Table2 AS T2_2 ON T2_2.T2Column_2 = T1.Id
LEFT JOIN Table2 AS T2_3 ON T2_3.T2Column_3 = T1.Id
LEFT JOIN Table2 AS T2_4 ON T2_4.T2Column_4 = T1.Id
LEFT JOIN Table2 AS T2_5 ON T2_5.T2Column_5 = T1.Id;
``````

Table 1 has Unique Values - it is Primary Key, Table 2 can have any number of values MANY. Table2 columns are not foreign keys of Table1 - they are values that must exist in Table1 but other than that - they are not FKeys.

So basically Count(Id) Where Id Value Is in T2, Cols 1..n

• What column is `IN (...)` ? Perhaps you mean `T1.id IN (...)`? Commented Jul 15, 2019 at 17:09
• @RickJames Yes - I missed that .. Commented Jul 15, 2019 at 17:10
• Make the col names consistent. Shorten the question to only 3 tables total. Shorten the column names. You say "not correct" -- too high? Too low? Are the tables 1:many? (or what)? Switch from `LEFT JOIN` to `EXISTS`? Commented Jul 15, 2019 at 17:13
• You probably wanted inner joins, not outer. Commented Jul 15, 2019 at 17:15
• @mustaccio No I do not want inner joins . With that it combines the joins and the resulting count is 0, I want to get a count for Id where it exists in the other T2.Columns 1 through x .. Commented Jul 15, 2019 at 17:22

## 1 Answer

Look for

``````SELECT COUNT(t1.Id) FROM Table1 As t1
{ INNER | LEFT } JOIN Table2 AS t2 ON t1.Id IN (t2.T2Column_1,
t2.T2Column_2,
t2.T2Column_3,
t2.T2Column_4,
t2.T2Column_5);
``````
• I will test this tomorrow when I am back at work. Commented Jul 16, 2019 at 5:32