Jump to content

Subquery, use results from one query in another


Adamhumbug

Recommended Posts

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.

Link to comment
Share on other sites

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 by Adamhumbug
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.