-1

I have two SQL queries.

Select * from users (Returns 74 rows)

id      firstname       lastname
1       vignesh         km
2       hariram         v
3       vijesh          g
.
.
74      ram             k


Select * from details where time between '2017-05-11 00:00:00' AND '2017-05-11 23:59:59' (Returns 5 row).


id      userid      logindate   
57      1           2017-05-11 09:07:23
63      3           2017-05-11 09:04:57
65      6           2017-05-11 09:14:45
83      13          2017-05-11 09:26:43
65      74          2017-05-11 09:11:53

i tried

SELECT *
FROM details dt LEFT JOIN users us ON us.id = dt.userid
WHERE dt.logindate BETWEEN '2017-05-11 00:00:00' AND '2017-05-11 23:59:59' OR dt.userid IS NULL
GROUP BY dt.userid; (6 rows returning - in that one row contains full of null)

AND

SELECT *
FROM details dt LEFT JOIN users us ON us.id = dt.userid
WHERE dt.logindate BETWEEN '2017-05-11 00:00:00' AND '2017-05-11 23:59:59'
GROUP BY dt.userid; (5 rows returning)


id      userid      logindate               firstname       lastname
57      1           2017-05-11 09:07:23     vignesh         km
63      3           2017-05-11 09:04:57     vijesh          g
65      6           2017-05-11 09:14:45     ..              ..
83      13          2017-05-11 09:26:43     ..              ..
65      74          2017-05-11 09:11:53     ram             k

Both are not working. But I need the 69 rows which are available in 1st query result and not available in the 2nd query result.

Vignesh KM
  • 1,979
  • 1
  • 18
  • 24
  • https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Yunnosch May 23 '17 at 05:02
  • `SELECT * FROM details dt LEFT JOIN users us ON us.id = dt.userid WHERE dt.userid IS NOT NULL AND dt.logindate BETWEEN '2017-04-26 00:00:00' AND '2017-04-26 23:59:59'` – Nimish May 23 '17 at 05:02
  • @Nimish Looks like an answer, why do you put it into a comment? – Yunnosch May 23 '17 at 05:03
  • https://stackoverflow.com/users/7393748/nimish the query you sent also returning 5 rows only – Vignesh KM May 23 '17 at 05:04
  • What is unsure and why do you not use comments to clarify your doubts? – Yunnosch May 23 '17 at 05:05
  • Now check Edited query – Nimish May 23 '17 at 05:05
  • @Vignesh clarify your question by providing sample input, desired output and more details on what you want. – Yunnosch May 23 '17 at 05:05
  • Make a [mcve], it should include a `.dump` from sqlite commandline tool, defining a useable toy database. You obviously had "only 5 rows" as one of your unwanted results, please explain, using the data in the toy db as reference. – Yunnosch May 23 '17 at 05:10
  • Please confirm login date is 11 may or 26 April ? – Nimish May 23 '17 at 05:17
  • sorry, its 11th may. I tried with different days. – Vignesh KM May 23 '17 at 05:19
  • have a look `SELECT * FROM users WHERE id NOT IN ( SELECT userid FROM details dt WHERE dt.userid IS NOT NULL AND dt.logindate BETWEEN '2017-05-11 00:00:00' AND '2017-05-11 23:59:59');` – Sathish May 23 '17 at 05:37

3 Answers3

2

Though I don't know why you're using group by, if you want to fetch users records which not exist in details, you can try this:

select *
from users u
left join details d on u.id = d.userid
and d.logindate BETWEEN '2017-05-11 00:00:00' AND '2017-05-11 23:59:59'
where d.id is null

or use not exists

select *
from users u
where not exists (
    select 1
    from details d
    where u.id = d.userid
    and d.logindate BETWEEN '2017-05-11 00:00:00' AND '2017-05-11 23:59:59'
)
Blank
  • 12,308
  • 1
  • 14
  • 32
-1
Select * from users us 
left join  details  dt on us.id=dt.userid
WHERE dt.logindate NOT BETWEEN '2017-04-26 00:00:00' AND
'2017-04-26 23:59:59' GROUP BY dt.userid
Piyush Kumar
  • 48
  • 1
  • 4
  • there is one more problem in this answer. if the user details are not available in details table at all i won't get that user. – Vignesh KM May 23 '17 at 05:19
  • you will get the user because it is left join.if user is not present in details than you will get null. i have taken user first than details. so i think you will get users – Piyush Kumar May 23 '17 at 05:33
-1

Don't use GROUP BY

SELECT * FROM details dt 
LEFT JOIN users us ON us.id = dt.userid 
WHERE dt.userid IS NOT NULL AND dt.logindate 
BETWEEN '2017-05-11 00:00:00' AND '2017-05-11 23:59:59
Nimish
  • 1,006
  • 1
  • 7
  • 19