How to correctly query my friend table with its many to many relationship with MySQL?-CodePudding

I am building a simple application using MySQL as the database. I have a user table with varying information, but I also have a friends table. Each user has it's own ID which is referenced in the friend table when a request is initiated.

My friends table is pretty simple:

id              int       NO    PRI                 auto_increment
date            datetime  NO    CURRENT_TIMESTAMP   DEFAULT_GENERATED
user_one_id     int       NO            
user_two_id     int       NO            
request_status  tinyint   NO

My issue is when trying to query my table to find a specific user's friends. My two subqueries in the below query both work fine on their own, however when I include them in the main query, I get no results. No errors, but also no results.

If the logged in user's ID is 1, then -

SELECT id, display_name, join_date, profile_image_url 
FROM user WHERE id IN
    (SELECT user_one_id FROM friend WHERE user_two_id = 1 AND request_status = 1) 
AND id IN 
    (SELECT user_two_id FROM friend WHERE user_one_id = 1 AND request_status = 1);

The query is supposed to account for the user's ID being in either column, depending on who actually initiated the friend request.

I apologize if this seems like a dumb question, but we all gotta learn sometime.

Please let me know if any additional information would help.

CodePudding user response：

I believe that your code would work if you used OR instead of AND for the conditions in the WHERE clause, but I propose a simpler solution with EXISTS:

SELECT u.id, u.display_name, u.join_date, u.profile_image_url 
FROM user u
WHERE EXISTS (
  SELECT * 
  FROM friend f 
  WHERE f.request_status = 1
    AND (f.user_one_id, f.user_two_id) IN ((u.id, ?), (?, u.id))
);

Replace ? with the id of the user that you want to search.