CODEWITHSUNDEEP
mysqlsql
Ram
Ram

Reputation: 1193

mysql avoiding subqueries

If I have a table with three columns

userid,eventid,entrytime

and values like 



userid,eventid,entrytime
  1      1      NULL
  2      1    2012-01-01
  1      2    2012-01-01
  2      2    2012-01-01
  3      1      NULL
  3      2      NULL

What is the best way of selecting userids where entrytime has always been NULL

Upvotes: 1

Views: 111

Answers (4)

Rumit Parakhiya
Rumit Parakhiya

Reputation: 2714

SELECT DISTINCT t1.UserId
FROM <TableName> t1
    LEFT JOIN <TableName> t2 ON t1.UserID = t2.UserID AND t2.EntryTime IS NOT NULL
WHERE t2.UserID IS NULL

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

select userid from
(select userid,entrytime,count(*) from yourtable 
group by userid,entrytime)a
group by userid
having count(*)=1 and max(entrytime) is null


SQl Fiddle Demo

Upvotes: 3

Michael Berkowski
Michael Berkowski

Reputation: 270617

Since a COUNT(entrytime) aggregate will eliminate NULL values, you can use it to determine which userid has no non-null value for entrytime by comparing it to 0 in the HAVING clause.

SELECT userid, COUNT(entrytime)
FROM yourtable
GROUP BY userid
HAVING COUNT(entrytime) = 0

Here is a live demonstration (Result is userid = 3)

Upvotes: 4

solaimuruganv
solaimuruganv

Reputation: 29697

    select userid from table_name  where entrytime is null

Upvotes: -1

Related Questions