Multiple value on between query [PostgreSQL]

Question

i have a simple query like this

select * from user where xxx between a and b

but how if i have multiple input, like xxx, yyy, mmm, bbb.

with same sintax,

select * from user where xxx,yyy,mmm,bbb between a and b

i want select all user that a and b is beetween xxx,yyy,mmm and bbb. so it will return all user that a and b is match with between creteria.

Thanks

If you realize that many columns have so much in common, then maybe they shouldn't be different columns. I don't know what the real deal is, but you may want to normalize your database model. — Vincent Savard
– Vincent Savard, Commented Aug 11, 2011 at 5:47
@Vincent Actually User Table have id, name,..., signuptime, and logoutime. i want to know users that loggin between 13:00 to 15:00 and (16:00 to 17:00). just in case, i want check more then 8 times creteria — Thessa
– Thessa, Commented Aug 11, 2011 at 6:04
You could for instance create a new table containing the user id, the date and the type of the date (sign up, logout, etc.). This looks like it would suit your needs. — Vincent Savard
– Vincent Savard, Commented Aug 11, 2011 at 6:07
so how to query that ? i'm new in sql. and i use PHP for database connection. — Thessa
– Thessa, Commented Aug 11, 2011 at 6:12

Jacob · Accepted Answer · 2011-08-11 05:44:47Z

3

SELECT * 
FROM user
WHERE (xxx BETWEEN a and b) 
AND (yyy BETWEEN a and b) 
AND (mmm BETWEEN a and b)  
AND (bbb BETWEEN a and b)

If you want all criteria to match. If you want atleast one of them to match, use OR instead of AND.

answered Aug 11, 2011 at 5:44

Jacob

43.6k6 gold badges81 silver badges81 bronze badges

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

3 Comments

Thessa Over a year ago

how if i have more value ? aaa,bbb,ccc,ddd,eee,rrr. more then 100 value

Thessa Over a year ago

Actually User Table have id, name,..., signuptime, and logoutime. i want to know users that loggin between 13:00 to 15:00 and (16:00 to 17:00). just in case, i want check more then 8 times creteria

Andriy M Over a year ago

@Thessa: Then your question is probably misleading. More likely you want something like xxx BETWEEN a AND b OR xxx BETWEEN c AND d OR …, not what you've asked.

Vincent Savard · Accepted Answer · 2011-08-11 06:17:44Z

0

I would create two extra tables to store your dates :

date_type (date_type_id, date_label)
user_date (user_id, date_type_id, date_value)

Which could be populated that way :

date_type (1, 'Sign in')
date_type (2, 'Sign out')
user_date (1, 1, '2010-08-11 02:14:20')
user_date (1, 1, '2010-08-11 02:36:54')
// etc

You would then have to do some JOIN to achieve what you want :

    SELECT *
      FROM user
INNER JOIN user_date
     USING (user_id)
INNER JOIN date_type
     USING (date_type_id)
     WHERE date_value BETWEEN a AND b;
       AND date_type_id IN (1,2) -- if necessary, you can choose which
                                 -- types to check

answered Aug 11, 2011 at 6:17

Vincent Savard

36.1k10 gold badges71 silver badges73 bronze badges

Collectives™ on Stack Overflow

Multiple value on between query [PostgreSQL]

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related