0

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 (...)? – Rick James Jul 15 at 17:09
  • @RickJames Yes - I missed that .. – StixO Jul 15 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? – Rick James Jul 15 at 17:13
  • You probably wanted inner joins, not outer. – mustaccio Jul 15 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 .. – StixO Jul 15 at 17:22
0

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. – StixO Jul 16 at 5:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.