Union woes..... 3

[jpo245]

Hi there, I'm having problems with this slightly more complex union query.

1. Users can be members of groups (via usergroupmap mapping table)
2. Groups can have zero users.
3. Users can be members of zero groups.

I'd like a record set that displays the follwing:

|groupname|username|
|ABCGroup|Mark| /*ABCGroup with 3 members*/
|ABCGroup|John|
|ABCGroup|Ted|
|BCDGroup|NULL| /*BCDGroup with 0 members*/
|CDEGroup|John| /*CDEGroup with 2 members*/
|CDEGroup|Ted|
|DEFGroup|Mark| /*DEFGroup with 1 members*/
|NULL|David| /Users who are not in any groups/
|NULL|Frank|
|NULL|Peter|

NOTE:
-Mark and John and Ted are members of more than 1 group!
-If users are not members of any groups, they are listed at the bottom with a NULL groupname column.
-if groups have no members, they have a NULL username column.




I made the following query, but it returns me ALL users, and ALL groups + members.



(SELECT
group.name as c1,
user.name as c2
FROM
group
LEFT JOIN usergroupmap on (group.id = usergroupmap.groupid)
LEFT JOIN user on (user.id = user.groupmap.userid)

WHERE user.id = 1234)

UNION

(SELECT
NULL as c1,
user.name as c2,

FROM
user

WHERE user.id = 1234)

is there any way to get ALL groups + members, and all users that are NOT members of groups...


Billion thanks....

 

[dalchri]

All you need to do to the second query is this:

SELECT
NULL as c1,
user.name as c2,

FROM
user
LEFT JOIN usergroupmap on (user.id = usergroupmap.userid)

WHERE user.id = 1234 And usergroupmap.userid Is Null

You might also want to user UNION ALL instead of plain UNION. Plain UNION will perform a DISTINCT on your resultset. I always use UNION ALL unless I am specifically trying to eliminate duplicates.

 

[r937]

nice post, dalchri, especially the UNION ALL advice

star for you





r937.com | rudy.ca