PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL – query joining data from two tables with one-to-many relation

I have two tables in my database.

  1. companies table with basic information about the company
| column_name   | data_type                |
| ------------- | ------------------------ |
| created_at    | timestamp with time zone |
| rating        | real                     |
| company_id    | uuid                     | primary key
| verified      | boolean                  |
| claimed       | boolean                  |
| street        | text                     |
| tags          | ARRAY                    |
| contact_phone | text                     |
| contact_email | text                     |
| website_url   | text                     |
| category      | text                     |
| subcategory   | text                     |
| zip_code      | text                     |
| name          | text                     |
| country       | text                     |
| logo          | text                     |
| city          | text                     |
  1. reviews Table that stores reviews of all companies
| column_name | data_type                |
| ----------- | ------------------------ |
| company     | uuid                     | foreign key relation to company_id in companies
| trust_score | smallint                 |
| created_at  | timestamp with time zone |
| id          | bigint                   |
| title       | text                     |
| review      | text                     |
| user        | text                     |

Now what I want to achieve is to get information from both of these tables as a single data set, where all reviews are added as an array set to a respective company.

I am a total beginner in SQL, so I’ve tried to find similar case on Stack Overflow, but couldn’t find one described clear enough so that I could understand it.

Tags:

2

Answers


  1. 0

    Try this:

    SELECT
    `companies`.`company_id`,
    `companies`.`rating`,
    `reviews`.`trust_score`
FROM `companies`
LEFT JOIN `reviews` ON
    `companies`.`company_id` = `reviews`.`company`

    You can use this to access the columns from both tables as though they were in the same table.

    Here’s an explanation of how the LEFT JOIN works:
    https://www.geeksforgeeks.org/postgresql-left-join/

    When a company has more than one entry in the reviews table, you’ll get multiple rows back from the query.

    For example, if the company with "company_id": 1 has two reviews, then you’ll get two rows back:

    {"company_id": 1, "companies_rating": 5, "trust_score": 4}
{"company_id": 1, "companies_rating": 5, "trust_score": 5}

    If a company with "company_id": 2 also has a review, then you’ll get three rows back:

    {"company_id": 1, "companies_rating": 5, "trust_score": 4}
{"company_id": 1, "companies_rating": 5, "trust_score": 5}
{"company_id": 2, "companies_rating": 3, "trust_score": 3}

    All rows with the same company_id will have the same company information (e.g. the same companies_rating), but different review information (e.g. a different trust_score).

    As you process these rows in your code, use the company_id to group the information for a single company together, building up a reviews array for the company as you go.

    Login or Signup to reply.
  2. 0

    You can get what you described with a simple inner join query:

    SELECT r.*, c.title FROM reviews r INNER JOIN companies c ON c.company_id = r.company ORDER BY c.id

    Note that this needs to be processed after getting fetched, to do your appropriate mapping for reviews and companies.

    Bonus: If you focus about companies and want to see all including those who do not have reviews you can select from companies table and use a left join or select from reviews table using right join. Same thing goes for reviews if you want all of them including those not related to any company. That is the SQL join principles, you can educate yourself about them.

    Please, let me know if this was helpful.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search