Seeking help on a complex SQL I reckon, wish get a simple one from answer

[Steven Jiang]

Hi All

Another sql to ask here. we have a table
File(name ,date, file_size).
anyone has simple query to list name of file where the related size is not 0

let me give some sample data.
name date size
a 1/1/2001 0
a 2/1/2002 2
a 3/3/2003 0
b 1/1/2000 3
b 2/2/2001 4

If I run the query, it should list "b" only as "a" has two related size = 0.

Thanks in advance.

I know a complex query already, just want a simple one.
my query is

select name from File where size>0 and name not in (select name from File where size=0)

[Balu Sadhasivam]

select distinct(name) from File where size!=0

more faster
select distinct(name) from File where size > 0

[Steven Jiang]

Balu Sadhasivam wrote:
select distinct(name) from File where size!=0

more faster
select distinct(name) from File where size > 0

thanks for that, but your answer is not right, if you run it against the table I created, you will get "a", "b", but "a" is related two records with '0' in size field.

[Sagar Rohankar]

I can't help you much, but I modified your original query little bit.

select name from File where size>0 and name not in (select distinct(name) from File where size=0) 

[Balu Sadhasivam]

Steven Jiang wrote:[you will get "a", "b", but "a" is related two records with '0' in size field.

oh yeah.