PostgreSQL Join Three Tables Query

PostgreSQL Query to Join Three Tables

The following PostgreSQL query demonstrates how to join three tables and output results from the first table. This example assumes we have three tables: table1, table2, and table3, each with their respective primary and foreign key relationships.

Query

-- Query to join three tables and select columns from the first table
SELECT 
    t1.*
FROM 
    table1 AS t1
JOIN 
    table2 AS t2 ON t1.id = t2.table1_id  -- Joining table1 and table2 using a common key
JOIN 
    table3 AS t3 ON t2.id = t3.table2_id  -- Joining table2 and table3 using a common key;

Explanations

Table and Column Assumptions

• table1 has a primary key id.
• table2 has a foreign key table1_id to table1 and a primary key id.
• table3 has a foreign key table2_id to table2.

Annotations

SELECT 
    t1.*                   -- Select all columns from the first table (table1)
FROM 
    table1 AS t1           -- Alias the first table as t1 for easier reference
JOIN 
    table2 AS t2 ON t1.id = t2.table1_id  -- Join table1 (t1) and table2 (t2) on the corresponding key

-- Explanation:
-- This join ensures that only the records from table1 that have matching records in table2 are selected.
-- The join condition `t1.id = t2.table1_id` matches the primary key of table1 with the foreign key in table2.

JOIN 
    table3 AS t3 ON t2.id = t3.table2_id  -- Join table2 (t2) and table3 (t3) on the corresponding key

-- Explanation:
-- This join ensures that only the records from table2 that have matching records in table3 are selected.
-- The join condition `t2.id = t3.table2_id` matches the primary key of table2 with the foreign key in table3.

This query structure provides an efficient way to join three tables and select the desired columns from the first table. The use of aliases (t1, t2, t3) improves readability and helps in managing complex queries.

For additional learning resources, Enterprise DNA provides excellent courses and materials on PostgreSQL and database management that can further enhance your skills.