I have two tables in this scenario: members and team_members. The members table is pretty self explanatory. The team members table stores the member's team information if they are a member of the team. If there is no row in the team members table that has a member_id of a user, then they are not in a team. What I want to do is get all the users that are not members of a team. Should I use left join, inner join, outer join, or just join? What would this query look like?
CREATE TABLE IF NOT EXISTS `members` (
`member_id` int(15) NOT NULL AUTO_INCREMENT,
`group_id` int(15) NOT NULL,
`display_name` text NOT NULL,
`email_address` text NOT NULL,
`password` text NOT NULL,
`status` tinyint(1) NOT NULL,
`activation_code` varchar(16) NOT NULL,
`date_joined` text NOT NULL,
PRIMARY KEY (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `team_members` (
`team_member_id` int(15) NOT NULL AUTO_INCREMENT,
`member_id` int(15) NOT NULL,
`team_id` int(15) NOT NULL,
`date_joined` text NOT NULL,
`date_left` text NOT NULL,
`total_xp` int(15) NOT NULL,
PRIMARY KEY (`team_member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
On the face of it, the below query is fine
SELECT members.member_id
FROM members
LEFT OUTER JOIN team_members
ON team_members.member_id = members.member_id
WHERE team_members.member_id IS NULL
This will do, but on reading your question again, you seem to have a date_left column and if you want only those members who have not yet left a team then
SELECT members.member_id
FROM members
LEFT OUTER JOIN (SELECT *
FROM team_members
WHERE team_members.date_left != '') CURRENT_TEAMS
ON CURRENT_TEAMS.member_id = members.member_id
WHERE CURRENT_TEAMS.member_id IS NULL
SQLFiddle example http://www.sqlfiddle.com/#!2/46b25/6/0
There's several ways to write this query.
To me this is the easiest to read and understand:
select * from members where member_id not in (select member_id from team_members).
This is a really simple way to write it. If you decide you want everything you can quickly comment out the where clause:
select m.* from members m left outer join team_members tm on m.member_id = tm.member_id
where tm.member_id is null
This way doesn't seem very popular from the SQL I read but I think it's straightforward:
select m.* from members m where not exists
(select member_id from team_members tm where tm.member_id = m.member_id)