Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Consider a main table as

CREATE TABLE groups
(
group_id int(11) NOT NULL AUTO_INCREMENT,
group_title varchar(255),
PRIMARY KEY(group_id)
) ENGINE=InnoDB

and a second table for relationship as

CREATE TABLE group_members
(
relation_id int(11) NOT NULL AUTO_INCREMENT,
group_id int(11),
user_id int(11),
FOREIGN KEY(group_id) REFERENCES groups(group_id) ON DELETE CASCADE,
FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,
PRIMARY KEY(relationsh_id)
) ENGINE=InnoDB

How to get a full list of all groups with an additional column indicating that if a given user is member of each group or not. This means that we will have a WHERE clause indicating the corresponding user_id, and the additional column will show 0 or 1, depending that this user_id has a connection to each group or not.

share|improve this question
    
can you clarify your question..are you wanting to providing a user_id in the where clause and the additional column is 0 or 1 if the provided user is in the group? or get a list of all groups, with a column of all users in the group? –  Derek Downey May 22 '13 at 21:15
    
@DerekDowney the former one. I added description to the question. –  All May 22 '13 at 21:18
    
I've updated my answer, see if that's what you are expecting.. –  Derek Downey May 22 '13 at 21:30

1 Answer 1

up vote 2 down vote accepted

Based on your updated question:

SELECT group_title, IF(user_id IS NULL, 0, 1)
FROM groups
LEFT JOIN (SELECT user_id, group_id 
          FROM group_members WHERE user_id=1)gMembers USING(group_id)

sqlfiddle

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.