CODEWITHSUNDEEP
sqlsqlite
Alexis Romot
Alexis Romot

Reputation: 534

Querying rows with coupled conditions

I've got an SQLite table where I store user settings with the following schema :

CREATE TABLE settings (
  [id] varchar(50) NOT NULL,
  [token] varchar(20) NOT NULL,
  [value] varchar(40) NOT NULL,
  [created] datetime NOT NULL,
  [user_id] INT(10) NOT NULL,
  PRIMARY KEY (id)
);

The name of the setting is [token]. The value of the setting is [value].

I'd like to SELECT users who have 2 specific settings (both) :

So I tried :

SELECT user_id FROM settings 
WHERE  
        (token = 'language' AND value = 'en')
    OR  (token = 'newsletter' AND value = 1) 
)
GROUP BY user_id

But it doesn't work as I get users which ONLY have one of the 2 conditions (I want both). How can I do?

Upvotes: 2

Views: 53

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

Simply aggregate your table per user_id and use HAVING to ensure both conditions are met for the user.

select user_id
from settings
group by user_id
having count(case when token = 'language' and value = 'en' then 1 end) > 0
   and count(case when token = 'newsletter' AND value = 1 then 1 end) > 0

You can add a WHERE clause to speed this up, if you like:

where (token = 'language' and value = 'en') or (token = 'newsletter' AND value = 1)

Upvotes: 1

Bhavika Zimbar
Bhavika Zimbar

Reputation: 481

Please try This query :

SELECT user_id 
FROM settings 
WHERE  (token = 'language' AND value = 'en')
        OR  (token = 'newsletter' AND value = '1')

There is no use of Group By clause

Upvotes: 0

Related Questions