CODEWITHSUNDEEP
sqlpostgresql
Yudhistira Maulana
Yudhistira Maulana

Reputation: 1

How do I join across multiple tables in Postgres?

I have 5 tables,

product: 
id | name | description | enable

category:
id | name | enable

category_product: 
id | product_id | category_id

image: 
id | name | files | enable

image_product:
id | product_id | image_id

I'd like my join table to resemble this:

product.name | product.description | category.name | image.name | image.file

Upvotes: 0

Views: 107

Answers (1)

sticky bit
sticky bit

Reputation: 37472

I guess you want to know how to join tables that have no direct relation but via other tables? Then the answer is to build a chain of joins over all the tables necessary.

SELECT p.name,
       p.description,
       c.name,
       i.name,
       i.file
       FROM product p
            INNER JOIN product_category pc
                       ON pc.product_id = p.id
            INNER JOIN category c
                       ON c.id = pc.category_id
            INNER JOIN image_category ic
                       ON ic.product_id = p.id
            INNER JOIN image i
                       ON i.id = ic.image_id;

Upvotes: 3

Related Questions