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)

Fosco · Accepted Answer · 2010-10-27 12:18:47Z

4

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)

edited Oct 27, 2010 at 12:18

answered Oct 26, 2010 at 12:30

Fosco

38.6k7 gold badges90 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Gopi Over a year ago

I am sorry still it returns zero

Fosco Over a year ago

@Sri ok! I changed the approach.. try this one out.

Fosco Over a year ago

@Sri can you provide more information? Maybe we're missing something, because this should work.

Gopi Over a year ago

But here I need the file count not the sum of isdeleted

Fosco Over a year ago

@Sri I added a query to get file count based on filename, let me know if you need anything else.

|

Andrew Carmichael · Accepted Answer · 2010-10-26 13:18:34Z

0

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.

answered Oct 26, 2010 at 13:18

Andrew Carmichael

3,1231 gold badge24 silver badges22 bronze badges

Comments

Andy Lester · Accepted Answer · 2010-10-26 17:29:13Z

0

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;

edited Oct 26, 2010 at 17:29

Andy Lester

94.2k16 gold badges106 silver badges162 bronze badges

answered Oct 26, 2010 at 12:33

Roadie57

3241 silver badge6 bronze badges

3 Comments

Roadie57 Over a year ago

Or even simpler "Select Count(isDeleted) FROM Table2 where Table1_ID = X and isDeleted =1"

Gopi Over a year ago

If I pass 1 or 2 or 3, I need to get the count as 2 based on isdeleted column in table 2

Roadie57 Over a year ago

Ahh... I misunderstood your intentions you want grouped by name not by ID Try FOSCOs answer above he seems on the right track

Collectives™ on Stack Overflow

Select query in MySQL

3 Answers 3

7 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related