CODEWITHSUNDEEP
mariadbheidisql
G. Langlois
G. Langlois

Reputation: 75

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

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!

Upvotes: 0

Views: 1881

Answers (2)

forpas
forpas

Reputation: 164089

You can do it with group by username and apply the conditions in a HAVING clause:

select username 
from User_Logins  
where
  date between '..........' and '..........'  
  and 
  hour between '..........' and '..........';
group by username
having 
  sum(page = 'Url_1') > 0
  and 
  sum(page = 'Url_2') = 0

Replace the dots with the date/time intervals you want.

Upvotes: 2

Theparkcaman
Theparkcaman

Reputation: 36

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.

Upvotes: 2

Related Questions