CODEWITHSUNDEEP
sqlpostgresqldatabase-design
Mladen
Mladen

Reputation: 51

Postgres join categories

I need to join subcategories to general categories, I have page with list of all categories and subcategories, subcategories have one category array ex. cat = {10,17}, that array contains ids of general categories. I need to join subcategories names to general categories. ex. 

cat_id = 1, -> general category ID
name = 'Music', -> general category name
subcats = {'Rock', 'Bla', 'Bla'} -> joined subcategories

This query list all categories and subcategories

SELECT * FROM event.kat k ORDER BY k.kat_id

Thanks!

Upvotes: 1

Views: 140

Answers (1)

yieldsfalsehood
yieldsfalsehood

Reputation: 3085

You should refactor your schema if possible. At the outset I see at least three tables: categories, subcategories, and category_subcategory, which acts as a join table between the first two tables. In that case it's easy to do "select ... from categories join category_subcategory join subcategories".

If you're absolutely stuck with this setup, you can do something like

select ...
from event.kat k
       join <subcategories table> s
         on k.kat_id = any (s.subcats)

Upvotes: 2

Related Questions