MySQL Merging User Lists With XOR/OR/AND

can you provide an example to what you like to do ...

AS mentioned above, there are 3 tables,  An I think it is clear enough but nevertheless

USERS -> Holds List of Users
GROUPS-> Holds List of Groups
USER_GROUPS -> Holds Which User Belongs to Which Groups

Assume We have  4 Users

UserW
UserX
UserY,
UserZ,

And 2 Groups
Group1,
Group2,

UserW is Member of Group1 & Group2,
UserX is Member of Group1
UserY is Member of Group1 & Group2
UserZ is Member of Group2

So When we do something like "Select USERS.* from USERS, USER_GROUPS WHERE USER_GROUPS.UID=''GROUP1"
This will give me a List of Users Who is member of Group1

when I do
"Select USERS.* from USERS, USER_GROUPS WHERE USER_GROUPS.UID=''GROUP1"
UNION
"Select USERS.* from USERS, USER_GROUPS WHERE USER_GROUPS.UID=''GROUP1"

This will give me List of Users That Exists in either Of the Group
What I want to do is
XOR : Get List of Users that Only Exists in One of the Group but not on Other (UserX and UserZ)
OR   : Get List of Users That Exists in Either One or Both Groups (W,X,Y,Z)
AND: Get List of Users That Exists in Both of the Groups(W,Y)
 
Thanks
Arron

send me dump from your tables to test the code on my machine before sending it to you

Here you are

DROP TABLE IF EXISTS `nserv`.`nserv_users`;
CREATE TABLE  `nserv`.`nserv_users` (
  `UID` char(32) NOT NULL,
  `USERNAME` varchar(50) NOT NULL,
  `PASSWORD` char(32) NOT NULL,
  `NAME` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`UID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
DROP TABLE IF EXISTS `nserv`.`nserv_user_group`;
CREATE TABLE  `nserv`.`nserv_user_group` (
  `UID` char(32) NOT NULL,
  `GROUP_UID` char(32) DEFAULT NULL,
  `USER_UID` char(32) DEFAULT NULL,
  PRIMARY KEY (`UID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
DROP TABLE IF EXISTS `nserv`.`nserv_groups`;
CREATE TABLE  `nserv`.`nserv_groups` (
  `UID` char(32) NOT NULL,
  `GROUP_NAME` varchar(100) DEFAULT NULL,
  `GROUP_DESC` text,
  PRIMARY KEY (`UID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Select allOpen in new window

>>> Merge Should happen in NAME Column Values not UID

Due to the relationships between the tables, this is just not an option for you.  The merge must necessarily work off the ID fields since that is the join field in the many-to-many relationship.  Testing on any other field will not necessarily yield the correct results since only UID is unique.

These haven't been tested, but they should point you along the path.


GIVEN:

Table1: SELECT * FROM nserv_users a INNER JOIN nserv_user_group b ON a.UID=b.USER_UID WHERE b.UID="GROUP1"
Table2: SELECT * FROM nserv_users c INNER JOIN nserv_user_group d ON c.UID=d.USER_UID WHERE d.UID="GROUP2"

AND:
SELECT * FROM Table1 e INNER JOIN Table2 f ON e.UID=f.UID WHERE e.UID IS NOT NULL AND f.UID IS NOT NULL

OR:
SELECT * FROM Table1
UNION
SELECT * FROM Table2

XOR:
SELECT * FROM Table1 e LEFT JOIN Table2 f ON e.UID=f.UID WHERE f.UID IS NULL
UNION
SELECT * FROM Table2 g LEFT JOIN Table1 h ON g.UID=h.UID WHERE h.UID IS NULL

Routinet,

Thanks for your response, I understand that it is not possible on NAME

I tried to execute the AND statement by Converting it to what I need but no luck with the Inner Join,


Any chance you can apply this Select Statements to the Tables above?

Thanks again
Arron.

What was your conversion, and what were the results?  I used your posted table definitions when creating that set of queries.

Routinet,

now I got a clear understanding.