mysql: get result from 2 tables, regardless of a field being present in both tables

Quite hard 开发者_开发问答to summarize my question in the title, so it's not totally covered..sorry for that :)

The problem is as follows: I have two tables, of which one is a user table, and a second is a join table which holds a user/group combination.

I have users who can subscribe to several groups and I want to be able to check if someone is already member of a certain group. I also want to be able to get user information, regardless of the user being a member of the concerned group.

So I have:

Table 1: userID, userName, etc...

Table 2: (with unique composite index) groupID, userID

My best try is this:

SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.userID = t2.userID
WHERE userName = 'John' AND groupID = 1

(to see if John is member of group 1)

I think the problem is in the WHERE clause. The query will only return users who are member of groupID = 1. But what I want is to have null returned if John is not a member of this group.

Any ideas, is this even possible? Thanks! Fab

---

You need to move the condition table2.groupID = 1 from the WHERE clause to the ON conditions (otherwise the LEFT JOIN is cancelled):

SELECT u.*
     , ug.groupID 
FROM table1 AS u
  LEFT JOIN table2 AS ug 
    ON  ug.userID = u.userID 
    AND ug.groupID = 1
WHERE u.userName = 'John' 

Another option is this approach (similar to LEFT JOIN):

SELECT *
     , ( SELECT ug.groupID
         FROM table2 AS ug 
         WHERE ug.userID = u.userID 
           AND ug.groupID = 1
       ) AS groupID
FROM table1 AS u
WHERE userName = 'John' 

---

You should move the WHERE part to the JOIN

SELECT * 
FROM table1 t1 
LEFT JOIN table2 t2 
    ON t1.userID = t2.userID 
    And userName = 'John' 
    AND groupID = 1

edit I generated my own data. Try this

With Users as
(
    Select 1 UserId, 'John' UserName
    Union Select 2, 'Tom'
),
Groups as
(
    Select 1 GroupId, 'Admin' GroupName
    Union Select 2, 'Power Users'
    Union Select 3, 'Users'
),
UserGroups as
(
    Select 1 UserId, 1 GroupId
    Union Select 2, 1
    Union Select 2, 3
)
Select *
From Groups
    Left Join (UserGroups
    Inner Join Users
        On Users.UserId = UserGroups.UserId
        And UserName = 'John'
        )
    On Groups.GroupId = UserGroups.GroupId

Results:

GroupId     GroupName   UserId      GroupId     UserId      UserName
----------- ----------- ----------- ----------- ----------- --------
1           Admin       1           1           1           John
2           Power Users NULL        NULL        NULL        NULL
3           Users       NULL        NULL        NULL        NULL

---

You probably want to do this:

SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.userID = t2.userID
WHERE userName = 'John'
HAVING groupID = 1 OR groupID IS NULL
ORDER BY groupID DESC
LIMIT 1