Select query in MySQL

Question

Two tables

Table1

ID FileName

1  abc
2  abc
3  abc
4  xyz

Table2

ID Table1_ID isDeleted
1   1        1
2   2        1
3   3        0
4   4        0

I need to get the count of filename for the isDeleted=1 by passing any ID of table1, i.e for all the values(1,2,3) of ID, i need the count as 2

I tried with the following query

SELECT COUNT(t1.FileName) FROM Table1 t1 
LEFT OUTER JOIN Table1 t11 ON t1.FileName=t11.FileName 
INNER JOIN table2 t2 ON t2.Table1_ID =t1.ID AND t2.isDeleted=1
WHERE t1.ID=X; 

X-1,2,3

This always returns 3.

Edit: I need to get the count of the filename from the first table by passing the ID from the first table. The count should be based on the isdeleted column in second table. The tables are related by the column ID (table1) and Table1_ID (table2)

Answer 1

Give this a shot:

select SUM(isDeleted)
from Table2 
where Table1_ID in (
  select ID from Table1
  where FileName = (select FileName
                    from Table1
                    where ID = 1)
)

Edit: to get file count:

select count(*) 
from Table1 a
join Table2 b on a.ID = b.Table1_ID and b.isDeleted = 1
where a.FileName = (select FileName
                   from Table1
                   where ID = 1)

Answer 2

SELECT COUNT(t1.FileName) FROM Table1 t1 
INNER JOIN table2 t2 ON t2.Table1_ID =t1.ID AND t2.isDeleted=1
WHERE t1.ID=X; 

Answer 3

This works for me:

declare @id int
set @id = 1 /*Or 2 or 3 or 4, etc.*/

select sum(isdeleted)
from table2
where table1_id in 
    (select id 
    from table1 
    where filename = (select filename 
                      from table1 
                      where id = @id))

Edit: I can't see how this is different from Fosco's answer.