We have 2 tables in our ticketing system. In this system, users can create tickets and any number of users can watch one or more tickets.

For which, we have set up 2 tables:

1. To store watch info - watch_table
2. To store ownership info - ownership_table

watch_table

ticket_id | watched_by
=======================
t1        | u1
t2        | u2
t2        | u1


ownership_table
ticket_id | owned_by
=====================
t1        | u1
t2        | u2

I want to extract the count of watches per user. But, exclude the watches on their own tickets.

For instance, in the above example the output should be:

output:

user_id | count
================
u1      | 1
u2      | 0 

I can create a query using sub-queries to do this. Something like below:

select watched_by as user_id, count(*) from watch_table wt where
(select count(*) from ownership_table where owned_by = wt.watched_by and ticket_id = wt.ticket_id) = 0
group by watched_by

My question is how can I do this with joins and which one is more performant?

CodePudding user response：

select w.userid, count(o.ticketid)
from watches w left join owners o
    on o.ticketid = w.ticketid and o.userid <> w.userid
group by w.userid

https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=19901f70822260cc943e6e51b42c7fbe

CodePudding user response：

Based on your sample data you can probably do this with an outer join

select t.owned_by as User_Id, Count(w.ticket_id) as count
from ownership_table t 
left join watch_table w on w.watched_by=t.owned_by and w.ticket_Id != t.ticket_Id
group by t.owned_by

An alternative could be a correlated subquery:

select Owned_By as User_Id, (
    select Count(*)
    from watch_table w
    where w.watched_by=t.Owned_by and w.ticket_Id != t.ticket_Id
    )
from ownership_table t