Jump to content

Subquery, use results from one query in another


Adamhumbug
 Share

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.

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.

 Share

×
×
  • 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.