CODEWITHSUNDEEP
sqldatabase
Ayusman
Ayusman

Reputation: 8729

Find distinct values from an inner query

How can I fetch distinct values from an inner query? Scenario:

I have a table: MyData with columns ID and Starttime. ID is a hex string and starttime is a time stamp. ID and starttime can be a null.

Here is how the table looks:

ID           StartTime
01655a70    2014-10-24 06:22:03.0
01655a70    2014-10-24 06:22:03.0
b752        2014-10-15 03:19:03.0
b752        <null>
3922b       2014-10-15 03:19:03.0
d98cb       <null>

I want to get distinct ID values that do not have any NULL value in it's starttime column.

Expected result should be:

01655a70
3922b

I have tried:

select distinct(ID) from Mydata where ID in (select ID from MyData where id not like '' and starttime is not null)


select distinct(inner.ID) from (select ID from MyData where id not like '' and starttime is not null) as inner

which seems to yield all the ID entries including the ones that have a null value.

Also looked at the SO posts:

http://stackoverflow.com/questions/23278387/options-for-returning-distinct-values-across-an-inner-join

and 

http://stackoverflow.com/questions/13149857/select-distinct-on-inner-join-query-filtering-by-multiple-values-on-a-single-col

The select distinct query seems straight forward to me, is there something obviously wrong here?

Additional Information: My DB is a MS Access DB, *.accdb type database.

Upvotes: 1

Views: 1092

Answers (1)

Multisync
Multisync

Reputation: 8797

select t.id from (
  select id, count(*) as n_all, 
             count(starttime) as n_time
  from Mydata 
  group by id
) t 
where t.n_all = t.n_time;

count(*) counts all rows
count(col) counts not null col values

Another option:

select distinct m1.id from Mydata m1
where not exists (select 1 from Mydata m2 where m2.id = m1.id and m2.starttime is null);

Your query:

select distinct(ID) from Mydata 
where ID in (select ID from MyData 
             where id not like '' and starttime is not null);

id not like '' this condition doesn't test for null. Use id is not null instead

The subquery just returns all the ids which have not null starttime. So your query doesn't check all the values of starttime for each id and it is equivalent to:

select distinct ID from MyData where id not like '' and starttime is not null;

The second query does the same thing as the first query - you just added an alias for your subquery.

Upvotes: 1

Related Questions