CODEWITHSUNDEEP
mysqlselect
MamaWalter
MamaWalter

Reputation: 2113

MySQL SELECT if match one value only

my request seems not so difficult but i am not sure about the best way to do it.

I have the following table:

userID  file
   1      1
   1      2 
   1      3
   2      1
   2      3
   3      2
   4      1
   4      2

And i would like to select UserID that only have the file number 2. In my example the result would be only 3.

Upvotes: 0

Views: 2553

Answers (4)

DRapp
DRapp

Reputation: 48169

One way is via a flag a flag of IF they have a #2 file in addition to total records for the user... if both = 1, then you are good. However, this version queries ALL users.

select
      userID
   from
      YourTable
   group by
      UserID
   having
          sum( if( file = 2, 1, 0 )) = 1
      AND count(*) = 1

This version applies a pre-query to ONLY list those people who AT A MINIMUM have a #2 file, then gets the total count

select
      PreQuery.UserID
   from
      ( select distinct YT1.userID
           from YourTable YT1
           where YT1.File = 2 ) as PreQuery
      JOIN  YourTable YT2
         on PreQuery.UserID = YT2.UserID
   group by
      PreQuery.UserID
   having
      count(*) = 1

I obviously don't know your data size (record counts), but if you have 10k users and 100k files, but only 250 have access to file #2, then this second query will only be concerned with the 250 users first, then kick out only those that have only that single explicit file base on HAVING count = 1.

Upvotes: 0

Devart
Devart

Reputation: 122002

Try this query -

SELECT userID FROM table
GROUP BY userID
HAVING COUNT(IF(file = 2, 1, NULL)) = COUNT(*)

+--------+
| userID |
+--------+
|      3 |
+--------+

Upvotes: 0

lc.
lc.

Reputation: 116528

You can use a WHERE NOT EXISTS (SQL Fiddle):

SELECT UserId
FROM theTable t
WHERE file = 2
AND NOT EXISTS(SELECT 1 FROM theTable tt WHERE t.UserId = tt.UserId AND file <> 2)
GROUP BY UserId

Or a self-antijoin (SQL Fiddle):

SELECT t.UserId
FROM theTable t
LEFT OUTER JOIN theTable tt ON t.UserId = tt.UserId AND tt.file <> 2
WHERE t.file = 2
AND tt.UserId IS NULL
GROUP BY t.UserId

Upvotes: 1

John Woo
John Woo

Reputation: 263803

SELECT userID
FROM tableName a
WHERE file = 2
GROUP BY userID
HAVING COUNT(*) = 
(
  SELECT COUNT(*)
  FROM tableName b
  WHERE a.userID = b.userID 
)

Upvotes: 5

Related Questions