CODEWITHSUNDEEP
sqlpostgresql
codeBarer
codeBarer

Reputation: 2388

What is the better way to perform inner self join using SQL?

Is there a way write an optimized version of the self join query I have below? I'm trying to find user chat room session activity of users who have logged out but visited specific chat rooms such as in the sample SQL query below where users who entered chatroom 1 and 3 and have exited.

DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl (
    id BIGSERIAL PRIMARY KEY,
    empno INT,
    activity VARCHAR(50)
);

INSERT INTO tbl (empno, activity) VALUES
(1, 'logged_in'),
(1, 'chatroom1'),
(1, 'chatroom3'),
(2, 'logged_in'),
(2, 'logged_out'),
(1, 'logged_out'),
(3, 'logged_in'),
(3, 'chatroom5');

Select t1.* from 
(Select empno, activity from tbl where activity in ('chatroom1', 'chatroom3')) as t1
JOIN 
(Select empno from tbl where activity ='logged_out') as t2
ON
t1.empno = t2.empno

I wrote the above script using PostgreSQL but I'm looking for a better way for writing SQL self joins. I guess for Sub queries I could have used CTE.

Upvotes: 2

Views: 70

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

You can also use aggregation. If I understand correctly:

select empno
from tbl
group by empno
having sum( (activity = 'logged_out')::int ) > 0 and
       sum( (activity = 'chatroom1')::int ) > 0 and
       sum( (activity = 'chatroom3')::int ) > 0;

If you mean either chatroom and not both, I would use:

having sum( (activity = 'logged_out')::int ) > 0 and
       sum( (activity in ('chatroom1', 'chatroom3') )::int ) > 0

Upvotes: 2

Darshan Mehta
Darshan Mehta

Reputation: 30829

You can use the following query:

SELECT t1.*
FROM tbl t1 JOIN tbl t2 ON t1.empno = t2.empno
WHERE t1.activity IN ('chatroom1', 'chatroom3')
AND t2.activity ='logged_out';

Upvotes: 3

Related Questions