Can anyone help me with this Query?

[DR Rom]

Basically my goal is to have a multiple filter in my small project that I'm working.
I have a page for Rooms or Lobby.

Filters are:

Privacy : ( 0 / 1 / 2 ) 0 = All / 1 = Public / 2 = Private

Status : ( 0 / 1 / 2 / 3 ) 0 = ALl / 1 = Open / 2 = In Progress / 3 = Completed

Created By Me: ( True or False )

Joined Room: ( True or False )

I also have 2 tables.. One for Rooms and other for Room_users


| User ID | Name |
| -------- | -------- |
| 1 | Me |
| 2 | Raven |
| 3 | Gabby |


| Room ID | Title | Privacy | Status | User ID |
| -------- | ----------- | -------- | -------- | ---------- |
| 1 | My Room | 1 | 1 | 1 | <<< Me/I'm the Creator/Host
| 2 | Raven Room | 2 | 1 | 2 | <<< Raven is the Creator/Host
| 3 | Gabs Room | 1 | 1 | 2 | <<< Raven is the Creator/Host


| Room Users | Room ID | User ID | Status |
| ---------- | ---------- | -------- | -------- |
| 1 | 2 | 1 | 1 |
| 2 | 2 | 3 | 2 |
| 3 | 3 | 3 | 1 |

Here, Me and UserID#3 joined to Room ID#2 (Raven Room)

**Status**: 1 = Pending / 2 = Joined

The result should display, Rooms based on the filter..
Here's my current query:

$userID = My ID / UserID# 1



$status = ($status != "0") ? "AND `a`.`status` = '".$status."'" : "AND `a`.`status` != '0'";
$privacy = ($privacy != "0") ? "AND `a`.`privacy` = '". $privacy ."'" : "AND `a`.`privacy` != '0'";



"SELECT a.room_id, a.title, a.status, a.privacy, a.user_id, b.room_id, b.user_id, b.status, c.user_id, c.name
FROM `rooms` as `a`
LEFT JOIN `room_users` as `b` ON `a`.`room_id` = `b`.`room_id`
LEFT JOIN `user` as `c` ON `b`.`user_id` = `c`.`user_id`
WHERE (`a`.`user_id` = '". $userID ."' OR (`b`.`status` != '1' OR `b`.`user_id` = '". $userID ."')) ". $status ." ". $privacy ." ORDER BY `a`.`room_id` DESC"

When i filter Privacy: All, it should return this ff:
1. all rooms that is public
2. all rooms that I'm member of (Private). Else, it should be exluded.

Filter: Privacy: Public should only display all rooms with status 1 (Public)

Filter: Privacy: Private should only display all rooms that **I'm member of**.

when I filter Created By Me, it should display only rooms that I created.

when I filter Joined Room, it should display only the rooms that I'm member of.

Right now, according to my query it only returns room that I joined in and the room I created.

And it displays multiple duplicated rooms, but I used array_unique to return only the unique rooms. I CAN'T use **GROUP BY** since it has a problem with MySQL regarding sql_mode=only_full_group_by and for some reason I dont have access on doing that.

Can anyone help me with how to construct the query for the database, so it will return the accurate data that i needed..


Any help is very much appreaciated.