Click here to Skip to main content
15,892,809 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
Posted
Comments
Peter Leow 20-Jun-14 9:14am    
What database are your using? SQL, MySQL?

SQL
select club_name from club where club_id not in
(select club_id from club_members where user_id = 'user1')
 
Share this answer
 
v2
Comments
mathiazhagan01 20-Jun-14 23:36pm    
Exactly, what I needed. I am a very bad query writer. Thank you Peter Leow for the query.
Peter Leow 21-Jun-14 1:23am    
You are welcome.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900