How to obtain distinct values based on another column in the same table?

Question

I'm not sure how to word the title properly so sorry if it wasn't clear at first.

What I want to do is to find users that have logged into a specific page, but not the other.

The table I have looks like this:

Users_Logins
------------------------------------------------------
| IDLogin | Username | Page  | Date       | Hour     |
|---------|----------|-------|------------|----------|
| 1       | User_1   | Url_1 | 2019-05-11 | 11:02:51 |
| 2       | User_1   | Url_2 | 2019-05-11 | 14:16:21 |
| 3       | User_2   | Url_1 | 2019-05-12 | 08:59:48 |
| 4       | User_2   | Url_1 | 2019-05-12 | 16:36:27 |
| ...     | ...      | ...   | ...        | ...      |
------------------------------------------------------

So as you can see, User 1 logged into Url 1 and 2, but User 2 logged into Url 1 only.

How should I go about finding users that logged into Url 1, but never logged into Url 2 during a certain period of time?

Thanks in advance!

Answer 1

I will try to improve the title of your question later, but for the time being, this is how I accomplished what you are asking for:

Query:

select distinct username from User_Logins  
where page = 'Url_1'  
and username not in  
    (select username from User_Logins  
    where Page = 'Url_2')  
and date BETWEEN '2019-05-12' AND '2019-05-12'  
and hour BETWEEN '00:00:00' AND '12:00:00';

Returns:

User_2

Comments:

1. I basically used a sub query to filter out the usernames you don't care about. :)
2. The time range is getting only 1 result, which you can test by removing the "distinct" in the first line of the query. If you then remove the time range from the query, you'll get 2 results.