CODEWITHSUNDEEP
mysql
Shawn
Shawn

Reputation: 11391

How can I get SQL to ignore a part of the WHERE clause when no result is found?

I'm sorry, I don't know how to ask this question without giving you the whole story:

Consider the following tables:

**users**
ID | name
1  | Shawn
2  | John
3  | Josh

**groups**
groupName | userID
groupA    | 1
groupA    | 2
groupB    | 1

This means that Shawn and John are part of group A, and Shawn is part of group B. Josh is part of no group at all.

What I want to do is to list every user and see to which group he belongs. Here is what I would try:

SELECT name, groupName FROM users, groups WHERE userID = ID GROUP BY name

Which outputs

name  | groupName
Shawn | groupA
John  | groupA

Here is my problem

I can't see that Shawn is part of group B as well. Nor can I see Josh at all.

my question

How can I get something like this instead?

name  | groupName
Shawn | groupA, groupB
John  | groupA
Josh  | none

Or at least something like this:

Shawn | groupA
Shawn | groupB
John  | groupA
Josh  |

I was thinking along theses lines:

SELECT name, groupName FROM users, groups WHERE (userID = ID GROUP BY name OR 1)

but I can't find the solution

Thanks you all in advance

Upvotes: 0

Views: 377

Answers (6)

Senseful
Senseful

Reputation: 91681

If you want the exact output:

name  | groupName
Shawn | groupA, groupB
John  | groupA
Josh  | none

Use:

SELECT name, 
  IFNULL(group_concat(groupName ORDER BY groupName SEPARATOR ', '), 'none') AS groupName
FROM users 
  LEFT JOIN groups ON (ID = userID)
GROUP BY name
ORDER BY ID

Upvotes: 0

idrosid
idrosid

Reputation: 8029

You can use the MySQL-specific function group_concat:

SELECT name, group_concat(groupName) 
FROM users, groups 
WHERE userID = ID GROUR BY name

Or, if you want all users to appear:

SELECT name, group_concat(groupName) 
FROM users LEFT JOIN groups ON userID = ID 
GROUP BY name

Upvotes: 2

harpo
harpo

Reputation: 43168

Look into MySQL's GROUP_CONCAT function.

Actually, I wish we had this in SQL Server.

Upvotes: 0

Powerlord
Powerlord

Reputation: 88796

GROUP BY name tells the DB you want one result per name. This clearly isn't what you want.

The last data you show is the easiest to get:

SELECT name, groupName FROM users u LEFT JOIN groups g ON u.ID = g.userID

Upvotes: 1

David
David

Reputation: 5456

You should do a left join if you want to see the users without any groups. And you don't need GROUP BY if you have no aggregation functions.

SELECT u.Name, g.groupName 
FROM Users u 
LEFT JOIN Groups g 
ON u.ID = g.UserId

Upvotes: 1

Cătălin Pitiș
Cătălin Pitiș

Reputation: 14341

select name, groupName from 
users left outer join groups on UserID = ID 
order by name

It will display nulls for group name when the user doesn't belong to any group.

Upvotes: 10

Related Questions