Postgres使用和条件将所有数组值匹配到同一列 [英] Postgres Match All array values to same column with and condition

问题描述

我有带有以下列的表table_a

I have table table_a with following columns

id  event_id
1   101
1   102
1   103
2   105
2   103
2   106

我想使用与条件类似的IN查询来搜索（101，103）和条件类似的条件

I want to search (101, 103) with and conditions similar to IN query with OR condition

让id表示另一个表event_categories的ID是异类的。 / p>

let say id is foreign of another table event_categories having relation like this.

id      parent_id
101     null
102     101
103     101
104     null
105     104

所以我想基于AND与父事件类别一起从table_a中获取记录，或在该父级的子事件类别内。

so I want to fetch records from table_a based on AND with parent event category, OR within sub-event categories of that parent.

附加的屏幕截图以进行更多说明

Attached screenshot for more clarification

例如美术馆和展览，节日，美食 &

For example "Art Galleries & Exhibits", "Festivals", "Food & Wine" with AND condition,

美术馆和所有子类别中的所有子类别。展品中的或条件工艺工作室或艺术与艺术学院

All sub-category of "Art Galleries & Exhibits" with OR condition "Art & Craft Workshops" OR "Art & Craft Fairs" OR "Visual Arts" if checked.

表事件

  id   
-------
 24445
 24446
 24447
 24448
 24449

event_categories 表

      id  |           name           | parent_id 
-----+--------------------------+-----------
  55 | Art &  Craft Workshops   |        25
  53 | Art & Craft Fairs        |        25
  25 | Art Galleries & Exhibits |          
  14 | Carnivals                |         3
  56 | Celebrations             |         3
  64 | Chili Cook-offs          |        26
  47 | Circus                   |        13
  45 | Comedy                   |        13

关联表事件和 event_categories
event_id是事件表的外键

 event_id | event_category_id 
----------+-------------------
    24590 |                 1
    24590 |                13
    24445 |                 1
    24445 |                13
    24591 |               1

推荐答案

第一个问题：

SELECT id
FROM table_a
WHERE id IN (101, 103)
GROUP BY id
HAVING COUNT(DISTINCT id) = 2

这篇关于Postgres使用和条件将所有数组值匹配到同一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！