0

Trying to figure out how find out which user records have at least 1 similar option enabled and then display these.

Tables:

user (id, username)
thing (id,title)
uthing (id, uid, tid)

Uthing table has a record of what options each user have selected.

uid = user.id
tid = thing.id

I need to compare the options (uthing) from user id 1 with all other users.

How can find users that have 1 or more options(uthing) of USER ID 1 and return these users with the options found?

This query will give me all options for user id 1.

select thing.title,thing.id as thingid 
from thing join uthing on uthing.tid = thing.id where uthing.uid = 1;

2 Answers 2

2

You achieve this by following this query

select uthing.id, uthing.uid, thing.title
from uthing left join thing on uthing.tid = thing.id
where uthing.tid in (
        select thing.id
        from thing join uthing on uthing.tid = thing.id
        where uthing.uid = 1
     );
1

Following your schema I've generated next example:

create table user (id int, username varchar(100));
create table thing (id int, title varchar(100));
create table uthing (id int, uid int, tid int);

insert into user values (1, 'user 1'),(2, 'user 2'),(3, 'user 3'),(4, 'user 4');
insert into thing values (1, 'thing 1'),(2, 'thing 2'),(3, 'thing 3'),(4, 'thing 4'),(5, 'thing 5');
insert into uthing values
(1, 1, 1),(2, 1, 3),
(3, 2, 2),(4, 2, 3),(5, 2, 5),
(6, 3, 4),
(7, 4, 1),(8, 4, 5);

This query select users where id <> 1 that has (exists) at least one thing in common with user id = 1.

select *
from   user u
join   uthing ut
on     ut.uid = u.id
join   thing t
on     t.id = ut.tid
where  u.id <> 1
and    exists (select 1
               from   uthing
               where  uid = 1
               and    tid = t.id);
id | username | id | uid | tid | id | title  
-: | :------- | -: | --: | --: | -: | :------
 2 | user 2   |  4 |   2 |   3 |  3 | thing 3
 4 | user 4   |  7 |   4 |   1 |  1 | thing 1

dbfiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.