I use postgresql IN operator as syntactic sugar to avoid using multiple “=” and “OR”. Here you can see how IN work–> https://www.techonthenet.com/postgresql/in.php
What i need is the same but to replace multiple “=” and “AND” operators
use ALL the same way you use IN
https://www.postgresql.org/docs/9.0/static/functions-subquery.html#AEN16897
in that case, just write FALSE as A = 1 AND A = 2 would never be true. Joking aside, you might want to have a look to row value comparison like (A, B) = (1, 2) (see here or here) - but I’m not sure what you are trying to accomplish in the end. Can you explain more?
thanks both for your anwers. what i am trying to do is just use syntactic sugar. instead of using WHERE id = 10 OR id = 15 or id = 9 i use WHERE id IN(10,15,9)
and now… instead of using WHERE id = 10 AND id = 15 AND id = 9 i can use WHERE id ALL(10,15,9)
but how can id be equal to 10 and 15 and 9 at the same time?!
I have two tables: orders and status.
I want to view the orders that contain ALL the following 3 associated states: 0010, 0015, 0009
it’s not logical, there is something missing… you can’t get all three values in a same record.
I did some experiments. Given the following schema (is this like you are using?)
[code][local] bussmann@~=# \d orders
Table “public.orders”
???
? Column ? Type ? Collation ? Nullable ? Default ?
???
? order_id ? integer ? ? not null ? ?
? date ? date ? ? ? ?
? customer ? text ? ? ? ?
? amount ? numeric ? ? ? ?
???
Indexes:
“orders_pkey” PRIMARY KEY, btree (order_id)
Referenced by:
TABLE “order_status” CONSTRAINT “order_status_order_id_fkey” FOREIGN KEY (order_id) REFERENCES orders(order_id)
[local] bussmann@~=# \d order_status
Table “public.order_status”
???
? Column ? Type ? Collation ? Nullable ? Default ?
???
? order_id ? integer ? ? ? ?
? status ? text ? ? ? ?
???
Foreign-key constraints:
“order_status_order_id_fkey” FOREIGN KEY (order_id) REFERENCES orders(order_id)
[local] bussmann@~=# TABLE orders;
???
? order_id ? date ? customer ? amount ?
???
? 1 ? 2017-01-01 ? customer 1 ? 100 ?
? 2 ? 2017-01-01 ? customer 1 ? 50 ?
? 3 ? 2017-01-01 ? customer 2 ? 75 ?
???
(3 rows)
[local] bussmann@~=# TABLE order_status;
???
? order_id ? status ?
???
? 1 ? 0010 ?
? 1 ? 0008 ?
? 1 ? 0100 ?
? 2 ? 0009 ?
? 2 ? 0010 ?
? 2 ? 0023 ?
? 2 ? 0015 ?
???
(7 rows)
[/code]
we can use an array_agg and array containment operator like in the following examples:
[local] bussmann@~=# WITH o_s AS (
[more]1(# SELECT
[more]1(# order_id,
[more]1(# array_agg(status) AS stati
[more]1(# FROM
[more]1(# order_status
[more]1(# GROUP BY
[more]1(# order_id
[more]1(# )
[more]2-# SELECT
[more]3-# o.*,
[more]4-# o_s.stati
[more]5-# FROM
[more]6-# orders o
[more]7-# LEFT OUTER JOIN o_s USING (order_id)
[more]8-# WHERE
[more]9-# o_s.stati @> ARRAY['0010', '0015', '0009']
[more]10-# ;
???????????????????????????????????????????????????????????????????????
? order_id ? date ? customer ? amount ? stati ?
???????????????????????????????????????????????????????????????????????
? 2 ? 2017-01-01 ? customer 1 ? 50 ? {0009,0010,0023,0015} ?
???????????????????????????????????????????????????????????????????????
(1 row)
[local] bussmann@~=# SELECT
[more]2-# o.*
[more]3-# FROM
[more]4-# orders o
[more]5-# WHERE
[more]6-# (
[more]6(# SELECT
[more]6(# array_agg(status)
[more]6(# FROM
[more]6(# order_status
[more]6(# WHERE
[more]6(# order_id = o.order_id
[more]6(# ) @> ARRAY [ '0010', '0015', '0009' ];
???????????????????????????????????????????????
? order_id ? date ? customer ? amount ?
???????????????????????????????????????????????
? 2 ? 2017-01-01 ? customer 1 ? 50 ?
???????????????????????????????????????????????
(1 row)
[local] bussmann@~=# SELECT
[more]2-# o.*
[more]3-# FROM
[more]4-# orders o
[more]5-# LEFT OUTER JOIN order_status
[more]6-# USING (order_id)
[more]7-# GROUP BY
[more]8-# order_id
[more]9-# HAVING
[more]10-# array_agg(status) @> ARRAY [ '0010', '0015', '0009' ]
[more]11-# ;
???????????????????????????????????????????????
? order_id ? date ? customer ? amount ?
???????????????????????????????????????????????
? 2 ? 2017-01-01 ? customer 1 ? 50 ?
???????????????????????????????????????????????
(1 row)however w/o using arrays, I’m not immediately seeing a nice solution:
[local] bussmann@~=# SELECT
[more]2-# o.*
[more]3-# FROM
[more]4-# orders o
[more]5-# WHERE
[more]6-# EXISTS (
[more]6(# SELECT *
[more]6(# FROM order_status
[more]6(# WHERE order_id = o.order_id
[more]6(# AND status = '0010'
[more]6(# ) AND EXISTS (
[more]6(# SELECT *
[more]6(# FROM order_status
[more]6(# WHERE order_id = o.order_id
[more]6(# AND status = '0015'
[more]6(# ) AND EXISTS (
[more]6(# SELECT *
[more]6(# FROM order_status
[more]6(# WHERE order_id = o.order_id
[more]6(# AND status = '0009'
[more]6(# )
[more]7-# ;
???????????????????????????????????????????????
? order_id ? date ? customer ? amount ?
???????????????????????????????????????????????
? 2 ? 2017-01-01 ? customer 1 ? 50 ?
???????????????????????????????????????????????
(1 row)
[code]“ORDERS” TABLE
id_order | id_client | id_product
or_001 c_003 p_002
or_002 c_003 p_003
or_003 c_001 p_003
or_004 c_002 p_001
“STATUS_TRACKING” TABLE
id_status | id_order | id_status | status_timestamp
st_001 or_001 s_001 2001-01-01 01:01:01
st_002 or_001 s_002 2001-01-01 01:01:01
st_003 or_001 s_003 2001-01-01 01:01:01
st_004 or_001 s_004 2001-01-01 01:01:01
st_005 or_002 s_001 2001-01-01 01:01:01
st_006 or_002 s_002 2001-01-01 01:01:01
st_007 or_002 s_003 2001-01-01 01:01:01
st_008 or_004 s_001 2001-01-01 01:01:01
st_009 or_004 s_004 2001-01-01 01:01:01
st_010 or_004 s_001 2001-01-01 01:01:01[/code]
i want to list the id_order from “ORDERS” TABLE where
status has passed through states s_002 AND s_003 AND s_004
on this case i should get ONLY or_001
I would say something with array_agg
but ALL will not work.
if you are joining the tables then perhaps
if a.id in(10,15,9) OR b.id in(10,15,9) OR c.id in(10,15,9)There are two tables with the name id_status. Surely, that can’t be correct. Also, it would be far easier to have one column per type of status. Example: 0009 = Pricing_OK, 0010 = Processed, 0015 = Billed. I would use the following:
id_order Pricing_OK Processed Processed_Time Billed Billed_Time
1001 True True 20180809154503 False
1002 True False False
1003 True True 20180808223344 True 20180808110234
SELECT id_order, COUNT(DISTINCT id_status) FROM status_tracking WHERE status_id IN("S_002","S_003","S_004")
group by id_order
HAVING COUNT(distinct id_status)=3