I have complex SQL QUERY I need some Help for the following conditions:
I'm looking for an SQL query with the below conditions:
SELECT * FROM Table_1,Table_2 where user_id = ’16’ // 16 = ($_SESSION[ 'SESS_USER_ID' ])
Sample of the result I am expecting below: WHAT I NEED-> I need the results showing all the members for login 'user_id' that are not in Table_2 'friend_status' CONDITION (0,1,2)
In the below table results, you can see that login 'user_id' (16 in this example) cannot see other 'user_id' that are in Table_2 CONDITION (0,1,2) including himself as the login 'user_id' (16)
//Perfect SQL results for me.
+—————————+—————————————+
+ user_id +signupdate +
+—————————+—————————————+
+ 23 + 2020-11-30 +
+—————————+—————————————+
+ 24 + 2020-11-30 +
+—————————+—————————————+
+ 28 + 2020-11-30 +
+—————————+—————————————+
+ 21 + 2020-11-30 +
+—————————+—————————————+
WHAT I NEED -> I need the an SQL QUERY to show results showing all the members for login 'user_id'(16) that are not in Table_2 'friend_status' CONDITION (0,1,2)
PROBLEM-> The Results I am getting below don't match the requested 'friend_status' CONDITION (0,1,2) of Table_2.
WHY? Because login 'user_id' should no see all the 'friend_status' CONDITION (0,1,2) in the full members page results that have NO CONDITION (0,1,2)
WHY? Because These SQl results CONDITION (0,1,2) are displayed elsewhere in 3 differents results pages for each CONDITION (0,1,2) -> Not todays topic.
//Wrong SQL results for me.
+—————————+—————————————+
+ user_id +signupdate
+—————————+—————————————+
+ 16 + 2020-11-30 + <— SHOULD DO NOT SHOW, CONDITION-> ITS ME “login” $user_id = ’16’
+—————————+—————————————+
+ 23 + 2020-11-30 +
+—————————+—————————————+
+ 24 + 2020-11-30 +
+—————————+—————————————+
+ 25 + 2020-11-30 +<— SHOULD NOT SHOW -> TABLE_2 CONDITION user_id = ( $_SESSION[ 'SESS_USER_ID' ])=’16’ AND CONDITION2 friend_status= ‘0’(Pending approval Mutual friends)
+—————————+—————————————+
+ 26 + 2020-11-30 +<— SHOULD NOT SHOW -> TABLE_2 CONDITION user_id = ( $_SESSION[ 'SESS_USER_ID' ])=’16’ AND CONDITION2 friend_status= ‘0’ (Pending approval Mutual friends)
+—————————+—————————————+
+ 27 + 2020-11-30 +<— SHOULD NOT SHOW -> TABLE_2 CONDITION user_id = ( $_SESSION[ 'SESS_USER_ID' ])=’16’ AND CONDITION2 friend_status= ‘1’ (Mutual friends)
+—————————+—————————————+
+ 28 + 2020-11-30 +<— SHOULD NOT SHOW -> TABLE_2 CONDITION user_id = ( $_SESSION[ 'SESS_USER_ID' ])=’16’ AND CONDITION2 friend_status= ‘2’ (Blocked)
+—————————+—————————————+
+ 21 + 2020-11-30 +
+—————————+—————————————+
TABLE_2 (table for friends relationship) friend_status=‘0’ -> This means 'user_id' pending friendship acceptance by 'user_id_resquest' -> Do not Show in full members list for login 'user_id' friend_status=‘1’ -> This 'user_id' and 'user_id_resquest' are already mutual friends. -> Do not Show in full members list for login user_id friend_status=‘2’ -> This means 'user_id' is blocked by 'user_id_resquest' -> Do not Show in members list for current login user_id
+——————————————+—————————+——————————————+—————————————————+
+friendship_id + user_id + user_id_request + friend_status+
+——————————————+—————————+——————————————+—————————————————+
+ 1 + 16 + 26 + 0 +//0->Pending
+——————————————+—————————+——————————————+—————————————————+
+ 2 + 16 + 25 + 0 +//0->Pending
+——————————————+—————————+——————————————+—————————————————+
+ 3 + 16 + 27 + 1 +//1->Mutual
+——————————————+—————————+——————————————+—————————————————+
+ 4 + 21 + 27 + 1 +//1->Mutual
+——————————————+—————————+——————————————+—————————————————+
+ 5 + 16 + 28 + 2 +//2 ->blocked
+——————————————+—————————+——————————————+—————————————————+
TABLE_1 (table of all the users)
+—————————+—————————————+
+ user_id + signupdate +
+—————————+—————————————+
+ 16 + 2020-11-30 +
+—————————+—————————————+
+ 23 + 2020-11-30 +
+—————————+—————————————+
+ 24 + 2020-11-30 +
+—————————+—————————————+
+ 25 + 2020-11-30 +
+—————————+—————————————+
+ 26 + 2020-11-30 +
+—————————+—————————————+
+ 27 + 2020-11-30 +
+—————————+—————————————+
+ 28 + 2020-11-30 +
+—————————+—————————————+
First of all, get rid of the comma-join and replace it with a JOIN; a LEFT JOIN in particular. Besides, you're basically doing a CROSS JOIN by not adding a WHERE on matching columns between the two tables. I'm not sure if that's what you intended to do though.
Try with this query:
SELECT COALESCE(t2.user_id,t1.user_id) AS user_id,
t1.signupdate,
CASE
WHEN t2.user_id='16'/*($_SESSION[ 'SESS_USER_ID' ])*/
AND friend_status IN (0,1) THEN 0
WHEN t1.user_id='16'/*($_SESSION[ 'SESS_USER_ID' ])*/ THEN 0
ELSE 1 END AS Chk
FROM Table_1 AS t1 LEFT JOIN Table_2 AS t2
ON t1.user_id = t2.user_id_request
HAVING Chk=1;
In the query, I've used LEFT JOIN on table_1's user_id column matching with table_2's user_id_request column. This is because if I match them between user_id in both table, table_2 will only show where user_id=16 and the rest as NULL, therefore those data that you want to filter from table_2 will not appear. In the SELECT .. section of the query, I've used two operations, COALESCE and a CASE expression. The CASE expression is used to filter out the current user_id and give it 0(zero) if it matches the condition and gives it 1 for other data that didn't match. This I assigned alias Chk and uses it at the end of the query on HAVING. For the COALESE part, I'm returning any user_id from table_2 first, then return user_id from table_1 if the table_2 is NULL.
Edit: Without using HAVING, check the CASE expression at WHERE like:
SELECT COALESCE(t2.user_id,t1.user_id) AS user_id,
t1.signupdate
FROM Table_1 AS t1 LEFT JOIN Table_2 AS t2
ON t1.user_id = t2.user_id_request
WHERE CASE
WHEN t2.user_id='16'/*($_SESSION[ 'SESS_USER_ID' ])*/
AND friend_status IN (0,1) THEN 0
WHEN t1.user_id='16'/*($_SESSION[ 'SESS_USER_ID' ])*/ THEN 0
ELSE 1 END=1;
It appears you basically need a left-join from the user table to the who is friended or pending. You are not clear if the person is marked as an ID = 2. If they are marked as blocked, would you not want their name to show up as a possible friend/friend request anyhow? So in short, you want anybody who is not your (current logged-in user) and has not already been requested as friend = 0, confirmed as friend = 1, OR already requested but BLOCKED user = 2. Why show someones name you wanted blocked.
select
T1.*
from
Table1 T1
where
NOT T1.User_ID = ($_SESSION[ 'SESS_USER_ID' ])
AND T1.User_ID NOT IN ( select T2.user_id_request
from Table2 T2
where T2.User_ID = ($_SESSION[ 'SESS_USER_ID' ]))
order by
T1.SignupDate
Just make sure you are properly parameterizing your queries and not using direct variables to build your query strings. You will be wide-open to sql-injection.
I am not sure which of the following will perform better with your real project data, so I recommend that you benchmark both to see if one performs noticeably better than the other.
Codes: (Demo)
Subquery on every row of Table_1 needing 2 placeholders in a prepared statement (might not perform well)
SELECT user_id, signupdate
FROM Table_1 AS t1
WHERE user_id != 16
AND NOT EXISTS(
SELECT 1
FROM Table_2
WHERE user_id_request = t1.user_id
AND user_id = 16
);
Left join needing only 1 place holder in a prepared statement:
SELECT t1.user_id, t1.signupdate
FROM Table_1 AS t1
LEFT JOIN Table_2 AS t2 ON t1.user_id = t2.user_id_request
WHERE 16 NOT IN (t1.user_id, COALESCE(t2.user_id,0))
AND t2.friendship_id IS NULL;
Both output:
| user_id | signupdate |
| ------- | ---------- |
| 23 | 2020-11-30 |
| 24 | 2020-11-30 |
| 21 | 2020-11-30 |