I have two table in my database namely clubs and club_members.
table 'club' contains 'club_id' and 'club_name', this table contains each club id and club name
Like this:
club_id club_name
-----------------------
club1 clubName1
club2 clubName2
table 'club_members' contains 'club_id', 'user_id', this table contains user ids and club ids
Like this:
club_id user_id
-----------------------
club1 user1
club2 user1
club1 user2
If a user search for club names, I have to display the club names which he is not joined.
I have tried this query, I don't know where I am doing mistake
select distinct t1.club_id
from clubs as t1
right join
(
select club_id from club_members where user_id != 'user1'
)
as t2
on t1.club_id = t2.club_id;
I am expecting nothing should display for the above data in the table, but it display all club names.
Should I change the query or change my table? I am expecting not to change my tables.