0

Will try to do my best to explain this, as I don't really know how to word this to search for an answer. I'm a constantly learning but still newish SQL user who thus far has been able to solve most questions by searching, but can't think how to word this.

I want to pull a query, which is all contained in one single table. The database is logging rows as follows;

User    Login Type

Bob        1

Ryan       1

Tom        1

Tom        3

I want the query to show me the rows that have a "1" login type immediately followed by a "3" login type. So my query would result would look like this:

User  Login Type

Tom    1
Tom    3

How would I format such a query? Normally something like this would be easy by doing a few joins, but I can't seem to wrap my head around how to do it with only one table involved. I've looked into joining the table into itself, but can't seem to nail down how to get the results I want (if that is even what I should be doing, it is the only thing that seems logical to me)

  • 1
    Edit your question with your db tag. – Mihai Aug 14 '14 at 19:53
  • 2
    By "immediately followed by", how are the rows ordered? Do you only want to display users that have at least on row with Login Type=1? Also, a table definition would probably also be helpful. And must there always be a Login Type=3 following? Please update your question for the sake of clarity. If your question pertains to a specific database platform, please add this to the tags as well. – Daniel Hutmacher Aug 14 '14 at 20:29
  • You can do this with a window function such as lead, but you need to be able to order your rows. Are your rows date and time stamped? – Colin 't Hart Aug 14 '14 at 21:36
  • @Colin'tHart they are time stamped as well as sequentially ordered by an ID number. That said, I believe the answer below may have solved my issue. – Matt8 Aug 14 '14 at 21:42
1

I've used MySQL, but it should apply to virtually all database servers.

CREATE TABLE `test1` 
(
  `user` varchar(10) DEFAULT NULL,
  `logintype` int(11) DEFAULT NULL
);



mysql> SELECT * FROM test1;
+------+-----------+
| user | logintype |
+------+-----------+
| Bob  |         1 |
| Ryan |         1 |
| Tom  |         1 |
| Tom  |         3 |  <<== ALSO WORKS IF YOU HAVE ANOTHER 
+------+-----------+       RECORD TOM, 5
4 rows in set (0.00 sec)

mysql> 

SELECT t1.user, t1.logintype  
FROM test1 t1 
INNER JOIN test1 t2  
ON t1.user = t2.user 
AND t1.logintype != t2.logintype 
GROUP BY t1.user, t1.logintype  
ORDER BY logintype;    

+------+-----------+
| user | logintype |
+------+-----------+
| Tom  |         1 |
| Tom  |         3 |
+------+-----------+
  • I believe your suggestion has helped me reach a solution. I created this query, which seems to get the results I wanted: ` SELECT test1.timestamp, test1.user_name, FROM table test1 INNER JOIN table test2 ON test1.user_name = test2.user_name AND test1.login_type != test2.login_type AND test1.timestamp != test2.timestamp WHERE test1.login_type = '1' AND test2.login_type = '3' GROUP BY test1.user_name, test1.timestamp` Thanks for your help. – Matt8 Aug 14 '14 at 21:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.