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