Mysql query for selecting name based on condition

Question

Users             UserGroup

id  name      rowid    GrpID   UserID   name
1   aaa         1        1      1        aaa
2   bbb         2        1      3        ccc
3   ccc         3        2      1        aaa
4   ddd         4        2      4        ddd
5   eee         5        2      5        eee

I have 2 table, user and usergroup, I want to display all the name from user table but except the userID which is associated with GrpID in the userGroup Table.

for grpID 1 i want to display
bbb
ddd
eee

I dont want to display userid 2 and 3 because it in grpID 1. I will have many grpID. How to do it in my sql. I dont want to display the name that Is already in UserGroup Table for GrpID 1.. and same condition for other GroupID

Mosty Mostacho · Accepted Answer · 2011-10-04 22:46:35Z

1

What about?

select name from users u
where u.id not in (
    select userid from usergroup ug
    where ug.grpid = 1)

answered Oct 4, 2011 at 22:46

Mosty Mostacho

43.6k16 gold badges99 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rene Pot · Accepted Answer · 2011-10-04 22:39:22Z

0

Select * from users where userid <> '1'

As <> means: is not

edited Oct 4, 2011 at 22:39

answered Oct 4, 2011 at 22:33

Rene Pot

24.8k7 gold badges70 silver badges92 bronze badges

1 Comment

Rene Pot Over a year ago

I changed grpid to userid, as the real reference is not in sql, based on your example

DwB · Accepted Answer · 2011-10-04 22:39:35Z

0

Join the tables in your sql.

Ansi Join (something like this):

select
  user.id,
  user.name
from
  user
  inner join usergroup on user.id = usergroup.rowid
where
  usergroup.grpid = __parameter goes here__

Oldschool join (via where clause):

select
  user.id,
  user.name
from
  user,
  usergroup
where
  user.id = usergroup.rowid and
  usergroup.grpid = __parameter goes here__

answered Oct 4, 2011 at 22:39

DwB

38.6k11 gold badges62 silver badges86 bronze badges

Comments

Emmanuel N · Accepted Answer · 2011-10-04 22:58:45Z

0

    select users.name from users, usergroup where  users.userid = usergroup.userid and usergroup.grpid <> :grpId

edited Oct 4, 2011 at 22:58

answered Oct 4, 2011 at 22:51

Emmanuel N

7,4592 gold badges30 silver badges37 bronze badges

Collectives™ on Stack Overflow

Mysql query for selecting name based on condition

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related