Subquery, use results from one query in another

[Adamhumbug]

Hi All,

I have the following table (ssm_chat_link)

+---------+---------+-------------------+
| chat_id | user_id | chat_surrogate_id |
+---------+---------+-------------------+
|       1 |       1 |                 1 |
|       1 |       2 |                 2 |
|       2 |       1 |                 3 |
|       2 |       3 |                 4 |
+---------+---------+-------------------+

I am wanting to select the user_id of every one that shares a chat_id with user_id = 1

I will then user this info to get the user names of these people and run a foreach in my php to create a button per person.

I am very stuck with this and although i am sure this is very simple once you know how....i do not.

Your help is always appreciated.

[benanamen]

You need to do a JOIN with the table the holds the user info. You only need one query.

[Adamhumbug]

15 minutes ago, benanamen said:

You need to do a JOIN with the table the holds the user info. You only need one query.

My thought was i would run one query to find all of the chat_ids that have user_id = 1

and then another query that selects other user_ids that share the chat_id.

I cannot work out how to do this simply (in one query).  It may be a "cant see the wood for the trees" moment...

Edited May 6, 2020 by Adamhumbug

[Barand]

try

SELECT DISTINCT
     u.firstname
  ,  u.lastname
FROM ssm_chat_link a 
     JOIN
     ssm_chat_link b USING (chat_id)
     JOIN 
     user u  ON a.user_id = u.user_id
WHERE b.user_id = 1;

 

Edited May 6, 2020 by Barand

[Adamhumbug]

19 hours ago, Barand said:

try

SELECT DISTINCT
     u.firstname
  ,  u.lastname
FROM ssm_chat_link a 
     JOIN
     ssm_chat_link b USING (chat_id)
     JOIN 
     user u  ON a.user_id = u.user_id
WHERE b.user_id = 1;

 

So that does workish...(i was not / am not familiar with self joins)

I see what you are doing here - but this is also selecting the person with id 1 and i would like them to be omitted from the results

[Adamhumbug]

When i run the following sql on the following table:

SELECT DISTINCT u.user_firstname, u.user_lastname, u.user_id
FROM ssm_chat_link cla
JOIN ssm_chat_link clb using (chat_id)
JOIN ssm_user u on cla.user_id = u.user_id
WHERE clb.user_id = 2

+---------+---------+-------------------+
| chat_id | user_id | chat_surrogate_id |
+---------+---------+-------------------+
|       1 |       1 |                 1 |
|       1 |       2 |                 2 |
|       2 |       1 |                 3 |
|       2 |       3 |                 4 |
|       3 |       2 |                 5 |
|       3 |       4 |                 6 |
+---------+---------+-------------------+

i would expect to see user id's 2 & 4

But i see 1, 2 & 4

I can see there being many follow up questions to this... sorry in advance

[Barand]

... WHERE b.user_id = 1
      AND a.user_id <> b.user_id;       -- ADD THE CONDITION

 

[Adamhumbug]

2 minutes ago, Barand said:

... WHERE b.user_id = 1
      AND a.user_id <> b.user_id;       -- ADD THE CONDITION

 

well there ya go....thanks very much!!!!!

[Barand]

7 minutes ago, Adamhumbug said:

i would expect to see user id's 2 & 4

I'd expect 1 & 4