[SOLVED] simple duplicate query not working

[bulgin]

I know this is basic stuff but I'm stumped and starting to learn.  I have one table, users and it contains a groupid.  In all cases users are listed in groupid = 2 or groupid = 4.  Sometimes they are listed in both groupid 2 AND groupid 4.

 

Why doesn't this work?

 

SELECT * FROM `users` WHERE groupid = 2 and groupid = 4;

 

I just want to find those users who are in both groups, 2 and 4.

 

Thanks

[jackpf]

You want an OR.

[bulgin]

I tried an OR and it shows ALL users, not just those who are in BOTH groupid

[mtoynbee]

What are the other columns in the table?

[bulgin]

table users:

 

Field  Type  Null  Default  Comments

userid int(11) No   

groupid int(11) No   

entered datetime Yes  NULL 

modified timestamp No  CURRENT_TIMESTAMP 

 

[kickstart]

Hi

 

Do I take it that a user could have 2 rows, one for each groupid (ie, userid is not the unique key)?

 

If so then give this a try:-

 

SELECT *
FROM users a JOIN users b 
ON a.userid = b.userid
WHERE a.groupid = 2
AND b.groupid = 4

 

All the best

 

Keith

[mtoynbee]

SELECT * FROM users WHERE (user_id IN (SELECT user_id FROM users WHERE groupid=2) AND user_id IN (SELECT user_id FROM users WHERE groupid=4))

 

 

[gurroa]

How about this?

SELECT *
FROM users
where groupid = 2 or groupid = 4
group by userid

[bulgin]

Hi.

 

They all worked but mtoynbee's gave me exactly what I wanted.  Thanks all!