How to bring data without registration in an sql query? [closed]

Question

How to bring data without registration in an sql query? [closed]

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

In a query sql would like to bring users who are not registered in a defined group, EXAMPLE:
I have 10 registered users in my database, but of those 10 there are 5 registered in the GRUPO1 , the idea is to show the other 5 that are not registered in that group.

I have the following query, but it does not show any results.

SELECT
users.userId,
users.userName,
users.userEmail
FROM
users
WHERE users.userId NOT IN 
(SELECT
usergroup.userGroupId,
usergroup.userGroup_groupId,
usergroup.userGroup_userId
FROM
usergroup
WHERE usergroup.userGroup_groupId = 1)

mysql sql query

asked by JDavid 18.01.2018 в 18:51

source

2 answers

How do I resolve a file upload error code 2? make a variable query generated within another variable

score 1 · Answer 1

Viewing the edited question, and that the usergroup table has the user ids: this would work:

SELECT
  users.userId,
  users.userName,
  users.userEmail
FROM users
WHERE users.userId NOT IN 
( SELECT usergroup.userGroup_userId
  FROM usergroup
  WHERE usergroup.userGroup_groupId = 1
) ug

Although it would be more efficient to use:

SELECT
  u.userId,
  u.userName,
  u.userEmail
FROM users u
LEFT JOIN  usergroup ug ON u.userId = ug.userGroup_userId AND ug.userGroup_groupId = 1
WHERE ug.userGroup_userId IS NULL

score 1 · Answer 2

In theory if you have users and groups, there is a possible design flaw, depending on the following: - If a user can be in more than one group and group can have more than one user, in reality it is a many-to-many relationship that requires a table that, for example, group_ users, where the primary key of each one is inherited of them and generate 1: N and N: 1 relations, plus the columns that are desired.

The aforementioned scheme commonly applies to examples of schools, where there are students, subjects, classrooms, teachers, etc., and each table of the aforementioned generates N: N relations and it is necessary to convert them into 1: N relationships and / or N: 1 and naturally you can get the information without problem

Table users: userId (Pk), u.userName, u.userEmail ... Other columns

Table groups: groupId (Pk), description ... Other columns

Table grups_users (breaks relationships N: N) user.userId (Fk) group.groupId / Fk) ... Other columns

In this way the entity-relationship design is normalized and queries are simpler

Select us.userId (Pk), us.userName, us.userEmail From users us, groups_users gu Where gu.groupId! = 1

I hope this helps.

Greetings !!!