[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 596
  • Last Modified:

MySQL Merging User Lists With XOR/OR/AND

Hello

I have 3 Tables,

Users [UID, NAME, SURNAME]

GROUPS[UID,GROUP_NAME]

and

USER_GROUPS [UID, USER_UID, GROUPUID]

I would Like to Merge 2 UserGroups and Create a New One from it, Using

OR:    User Exists in Either of the Group
XOR: User Exists in one list but not in other
AND: User Exists in both of the Lists

Merge Should happen in NAME Column Values not UID

Thanks
Arron
0
magicMush
Asked:
magicMush
  • 4
  • 3
  • 2
1 Solution
 
agamalCommented:
can you provide an example to what you like to do ...
0
 
magicMushAuthor Commented:

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
0
 
agamalCommented:
send me dump from your tables to test the code on my machine before sending it to you
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
magicMushAuthor Commented:
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;

Open in new window

0
 
Steve BinkCommented:
>>> 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
0
 
magicMushAuthor Commented:
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.
0
 
Steve BinkCommented:
What was your conversion, and what were the results?  I used your posted table definitions when creating that set of queries.
0
 
Steve BinkCommented:
Actually, with the AND, the WHERE statement is superfluous since an INNER JOIN will necessarily return only what appears in both tables.  Thus, this should yield the same result:

SELECT * FROM Table1 e INNER JOIN Table2 f

Given the appropriate replacements:

SELECT * FROM
   (SELECT * FROM nserv_users a INNER JOIN nserv_user_group b ON a.UID=b.USER_UID WHERE b.UID="GROUP1") e
      INNER JOIN
   (SELECT * FROM nserv_users c INNER JOIN nserv_user_group d ON c.UID=d.USER_UID WHERE d.UID="GROUP2") f
      ON e.UID=f.UID
0
 
magicMushAuthor Commented:
Routinet,

now I got a clear understanding.

0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now