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