CODEWITHSUNDEEP
mysql
John
John

Reputation: 487

Mysql query for selecting name based on condition

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

Upvotes: 0

Views: 615

Answers (4)

Emmanuel N
Emmanuel N

Reputation: 7449

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

Upvotes: 0

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

What about?

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

Upvotes: 1

DwB
DwB

Reputation: 38300

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__

Upvotes: 0

Rene Pot
Rene Pot

Reputation: 24815

Select * from users where userid <> '1'

As <> means: is not

Upvotes: 0

Related Questions