CODEWITHSUNDEEP
mysqlsql
user1549097
user1549097

Reputation: 23

Trouble with a SQL using between and multiple occurences

everyone. I'm new here and I have a question on SQL which I seem to think that it may be easy to do in SQL but I'm out of ideas and I could use some help with my problem.

So I have a table (sql also here http://www.sqlfiddle.com/#!2/b9a37/1/0)

With multiple occurences for each person, different id's with auto increment and I have 3 fields, "Category", "In" and "Out"

What I'm trying to implement is a way for me to know when some of the users on the table belongs to the system using the "In" and "Out" as the limits.

Example

In the above example I would like to know who belongs to the system in year 2010:

so for User A, he Left Category A in 2010 but joined in 2011 Category 2011 so he SHOULD be a user in 2010 with Category A

for User B he should NOT belong to the system because there's no new entry for 2010+

At the end I'm tring to have the following output for 2010

I'm not achieving the results I want, for example

SELECT * from users
WHERE (users.In<=2010 and users.Out=2010)

Outputs

But Roger and Miller left the system in 2010 so they should NOT be members in 2010, and Smith and Pablo changed their category in 2010+ so they should show in the output.

A few notes: Don't ask me why this solution is developed like this, I just stumbled on it and trying to solve this issue. I'm considering doing a function in php do filter the data based on the requirements that I want because i'm running out of ideas to solve this. If you find a way to implement this better than what I saw here, be my guest and i'm open to new ideas.

Thanks in advance.

Final Note: Perhaps using http://www.sqlfiddle.com/#!2/b9a37/1/0 and try to find the desired output will help you out.

Upvotes: 2

Views: 116

Answers (2)

Dave Richardson
Dave Richardson

Reputation: 5005

This appears to work:

 select * from users a, users b where a.name = b.name and a.out + 1 = b.out

However, it will not work correctly if more than one user has the same surname and it assumes that the out year is one less than the in year.

Upvotes: 0

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171421

Give this one a shot:

select Name, Category
from users
where `In` <= 2010 and (
        `Out` > 2010 or name in (
            select name
            from users
            where `In` = 2011
            )
        )

SQL Fiddle Example

Upvotes: 1

Related Questions