What I Have:
Table 1 : USERS (autoID, name, etc)
Table 2 : TROPHIES (autoID, name, etc)
Table 3 : VIEWS (userID, timestamp, etc)
Table 4 : CANDIDATES (userID, trophyID, etc)
What I Know: USERS.autoID & TROPHIES.autoID
How I Do It:
I have this TROPHIES table where I store different categories users can be nomitated to.
Each User can be nominated for 1,2 or more trophies from TROPHIES table.
In the VIEWS table I store each view of the profiles for each individual user with USERS.autoID, timestamp and other data.
In the CANDIDATES table I store the TROPHIES.autoID and USERS.autoID - this way I know which User is nominated for which Trophy.
What I Need to Know
Knowing USERS.autoID & TROPHIES.autoID I want to make a TOP based on the number of entries in the last 3 days for example in VIEWS table of all USERS that are listed in CANDIDATES table for that specific trophy and find out the POSITION on that top of a specific user.
So let's say the user with the autoID 1 is nominated to the TROPHY with the autoID 10 and has 100 entries in the VIEWS table on the last 3 days but there are other 3 users nominated to the TROPHY with the autoID 10 who have more than 100 entries in the last 3 days so...I need a select that would return the number 4.
My Questions:
Can I do that with 1 single SELECT query? If yes...how? If no...how could I make this query to spend as little resources as possible.
Thanks!
[EDIT]
Here is some data
TABLE 1 - USERS
+--------+-------+ | autoID | name | +--------+-------+ | 1 | user1 | | 2 | user2 | | 3 | user3 | +--------+-------+
TABLE 2 - TROPHIES
+--------+------------+ | autoID | name | +--------+------------+ | 1 | Baseball | | 2 | Basketball | | 3 | Boxing | +--------+------------+
TABLE 3 - VIEWS
+--------+--------+------------+ | autoID | userID | timestamp | +--------+--------+------------+ | 1 | 2 | 1551632970 | | 2 | 2 | 1551632971 | | 3 | 3 | 1551632972 | | 4 | 1 | 1551632973 | | 5 | 2 | 1551632974 | | 6 | 1 | 1551632975 | | 7 | 3 | 1551632976 | | 8 | 1 | 1551632977 | | 9 | 2 | 1551632978 | | 10 | 3 | 1551632979 | | 11 | 3 | 1551632980 | | 12 | 3 | 1551632981 | +--------+--------+------------+
TABLE 4 - CANDIDATES
+--------+--------+----------+ | autoID | userID | trophyID | +--------+--------+----------+ | 1 | 2 | 1 | | 2 | 3 | 3 | | 3 | 1 | 2 | | 4 | 1 | 1 | +--------+--------+----------+
In the end I want to be able to know on which position a User is for a specific trophy based on the entries from the VIEWS table.
Let's say I want to check the position of the USER with the autoID = 1 for Baseball (trophy which has autoID = 1) after timestamp 1551632972.
So...First we have to see which users are listed in this trophy so we can ignore the entries from the table VIEWS for the other users. Trophy with the autoID 1 (Baseball) has only two users listed - user1 and user2.
Now I want to see how many entries both have so I can be able to find out which is the position of the user1 on this top.
So if we select and count all the entries from the table VIEWS for user1 where timestamp is equal or bigger than 1551632972 we will get number 3 and if we do the same thing for the user2 we will get 2 and since 3 is bigger than 2, user1 will be on 1st place and user2 will be on the 2nd place.
I am searching for a way to get the place in the TOP for a specific user inside a specific sport using a single MySQL query (if possible) or finding the best solution to do so...
I found the solution...I will just leave it here in case someone else will need it.
SELECT
U.autoId,
U.name,
U1.position
FROM USERS U
JOIN (SELECT
@rownum := @rownum + 1 AS position,
U.autoId,
U.name,
COUNT(V.autoID) as "Nr"
FROM USERS U
JOIN VIEWS V ON V.userID= U.autoID
JOIN CANDIDATESC ON C.userID= U.autoID
JOIN (SELECT @rownum := 0) R
WHERE C.trophyID= 'id_of_trophy_wanted' GROUP BY U.autoID ORDER BY Nr DESC) as U1 ON U1.autoID =
U.autoID
WHERE U.autoID = 'id_of_user_wanted'
Thanks to the ones who tried to help!