SQL: how to join multiple 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 reffers to users.

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

result username(john33) activity.name(walking)

I would really appreciate if anyone could help composing the correct SQL statement to retrieve (generate) the result table.

(I'm using MSSQL).

Thanks!

Answer 1

A nice tutorial on SQL Joins.

Answer 2

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

Answer 3

hello,

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

Answer 4

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);

Answer 5

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

Answer 6

Here's a good post from Jeff Atwood:

Visual explanations of SQL join