Postgres Match All array values to same column with and condition

Question

I have table table_a with following columns

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

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

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

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.

Table events

event_categories table

      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

Relation table events with event_categories event_id is foreign key of events table

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

I answered your first question but the second one isn't entirely clear. Can you show us a complete example for the second case? — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 21, 2016 at 10:52
Can you provide sample from both tables and what you want the output to be. — Evan Carroll
– Evan Carroll, Commented Dec 22, 2016 at 5:36
@EvanCarroll updated table detail and want to fetch events based on event category — Vivek Kumar Singh
– Vivek Kumar Singh, Commented Dec 22, 2016 at 12:17

Tim Biegeleisen · Accepted Answer · 2016-12-21 10:51:29Z

0

First question:

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

answered Dec 21, 2016 at 10:51

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Collectives™ on Stack Overflow

Postgres Match All array values to same column with and condition

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related