How do I join multible tables?

Question

I have the following tables (and example values):

**user:**
user_id (1, 2, 3)
username (john33, reddiamond...)
password (pass1, pass2...)

**session:**
session_id (4,5, 6)
user_id (1, 2, 3)

**activity**
activity_id (1, 2)
name (running, walking...)

**user_activity**
user_activity_id (1, 2, 3, 4, 5)
session_id (4, 5)
activity_id (1, 2)

All columns with the same name are related. In the table user_activity there are rows which describe what is the session's activity and the activity refers to users.

However I would like to get the table which describes what the user is currently doing:

**result**
username(john33)
activity.name(walking)

What is the SQL statement that gets the result table?

(I'm using MSSQL).

Solution 1

SELECT u.username, a.name
FROM user_activity ua
INNER JOIN session s
ON ua.session_id = s.session_id
INNER JOIN user u
ON s.user_id = u.user_id
INNER JOIN activity a
ON ua.activity_id = a.activity_id

Solution 2

I assume session.session_id and user_activity.user_activity_id are IDENTITY columns, so they are monotonically increasing. Therefore they are unique and the greatest value indicates the most recent entry.

So what you need to do is:

• Match user to a corresponding row in session with the greatest session_id value (that is, no other row is found with the same user_id and a greater session_id).

• Then match that row in session to a corresponding row in user_activity with the greatest user_activity_id.

• Then match that row in user_activity to a correspond row in activity to get the name.

Here's a query that should achieve this (though I have not tested it):

SELECT u.username, a.name
FROM user u
 JOIN session s1 ON (u.user_id = s1.user_id)
 LEFT OUTER JOIN session s2 ON (u.user_id = s2.user_id 
   AND s1.session_id < s2.session_id)
 JOIN user_activity ua1 ON (ua1.session_id = s1.session_id)
 LEFT OUTER JOIN user_activity ua2 ON (ua2.session_id = s1.session_id 
   AND ua1.user_activity_id < ua2.user_activity_id)
 JOIN activity a ON (a.activity_id = ua1.activity_id)
WHERE s2.session_id IS NULL AND ua2.user_activity_id IS NULL;

Here's an alternative form of query that should get the same result, and might be easier to visualize:

SELECT u.username, a.name
FROM user u
 JOIN session s1 ON (u.user_id = s1.user_id)
 JOIN user_activity ua1 ON (ua1.session_id = s1.session_id)
 JOIN activity a ON (a.activity_id = ua1.activity_id)
WHERE s1.session_id = (
     SELECT MAX(s2.session_id) FROM session s2 
     WHERE s2.user_id = u.user_id)
 AND ua1.user_activity_id = (
     SELECT MAX(ua2.user_activity_id) FROM user_activity ua2 
     WHERE ua2.session_id = s1.session_id);

Solution 3

I assume from your statement of the desired result that you want to find the current activity foreach user. I am also assuming that a user may have many sessions and that the current session is the one with the highest session_id.

Of course if you only have one session per user and one user_activity record per user then that's not an issue and your accepted answer is fine.

The key issue here is to identify the latest user_activity record for each user and use that to get to the activity.

This can be accomplished as follows :-

SELECT  u.username,  
        a.name  
FROM    user_activity AS ua  
JOIN    session AS s ON ua.session_id = s.session_id  
JOIN    user AS u ON u.user_id = s.user_id  
JOIN    activity AS a ON ua.activity_id = a.activity_id  
WHERE   ua.user_activity_id IN (  
          SELECT  MAX(ua2.user_activity_id)  
          FROM    user_activity AS ua2  
          JOIN    session AS s2 ON ua2.session_id = s2.session_id  
          GROUP BY s2.user_id);  

The following test-data proves the SQL. It creates 4 users and 4 activities it then creates a user_activity record for each user all doing housework. It then sets three users to their normal activity.

INSERT INTO user (username) VALUES ('sneezy');  
INSERT INTO user (username) VALUES ('grumpy');  
INSERT INTO user (username) VALUES ('happy');  
INSERT INTO user (username) VALUES ('snow_white');  

INSERT INTO session (user_id) SELECT u.user_id FROM user AS u;  

INSERT INTO activity(name) VALUES ("Sneezing");  
INSERT INTO activity(name) VALUES ("Frowning");  
INSERT INTO activity(name) VALUES ("Smiling");  
INSERT INTO activity(name) VALUES ("Housework");  

INSERT INTO user_activity (session_id, activity_id)  
SELECT s.session_id, a.activity_id  
FROM   session AS s JOIN activity AS a  
WHERE   a.name IN ("Housework");  

INSERT INTO user_activity(session_id, activity_id)  
SELECT  s.session_id, a.activity_id  
FROM    session AS s  
JOIN    USER as u ON s.user_id = u.user_id  
JOIN    activity AS a ON a.name = 'Sneezing'  
WHERE   u.username = 'sneezy' ;  

INSERT INTO user_activity(session_id, activity_id)  
SELECT  s.session_id, a.activity_id  
FROM    session AS s  
JOIN    USER as u ON s.user_id = u.user_id  
JOIN    activity AS a ON a.name = 'Frowning'  
WHERE   u.username = 'grumpy' ;  

INSERT INTO user_activity(session_id, activity_id)  
SELECT  s.session_id, a.activity_id  
FROM    session AS s  
JOIN    USER as u ON s.user_id = u.user_id  
JOIN    activity AS a ON a.name = 'Smiling'  
WHERE   u.username = 'happy' ;  

This generates the following results

  snow_white Housework
  sneezy     Sneezing
  grumpy     Frowning
  happy      Smiling

Solution 4

I think it will be something like:

select u.username, a.name
from user u
join session s on u.user_id = s.user_id
join user_activity ua on ua.session_id = s.session_id
join activity a on a.activity_id = ua.activity_id

View more solutions