CODEWITHSUNDEEP
databasepostgresql
Radek Simko
Radek Simko

Reputation: 16126

How to effectively join 3 tables M:N

I have few tables:

Apps:

id | name | url_key
===================
1  | Hello World | hello-world
2  | Snake 2 | snake-2

Developers:

id | name | url_key
===================
1  | Mr. Robinson | mr-robinson
2  | Richard | richard
3  | Nokia | nokia

Apps-Developers-Assignment

app_id | developer_id
=====================
1      | 1
1      | 2
2      | 3

So as you may see, one app may have more than one developer.

What i actually need is to write a PostgreSQL query to select all apps with some usefull information about related developers. e.g. sth like this:

Hello World | 1-hello-world | Mr.Robinson<1-mr-robinson> /and 1 more/
Snake 2     | 2-snake-2     | Nokia<3-nokia>

So if the app has one developer, just append the developer's name, url key and id to the app, if it has more developers, append the first and append the information of "how many developers are there".

And what's most important - i need to eventually filter the results (e.g. not return apps from some specified developer).

I know it can be solved by write first query for apps itself and then send next query per each row, but not the nice way, i think...

If i just simply JOIN the table:

SELECT * FROM apps a
LEFT JOIN apps_developers_assignment ada ON a.id = ada.app_id
LEFT JOIN developers d ON ada.developer_id = d.id

Hello World | 1-hello-world | Mr.Robinson<1-mr-robinson>
Hello World | 1-hello-world | Richard<2-richard>
Snake 2     | 2-snake-2     | Nokia<3-nokia>

It returns duplicate apps... and i don't know how to filter these results by developer (as i wrote above).

Upvotes: 1

Views: 2097

Answers (2)

Phil Sandler
Phil Sandler

Reputation: 28036

Assuming you only want to filter out one developer, you will need to pass a parameter. I am calling this parameter/variable @excludeDevId for this purpose.

I am much more familiar to T-SQL, so this may/will also need the equivalent syntax in Postgres. Also, I am leaving nulls as a result of the left join as null--from your output you probably want to COALESCE (probably some other output tweaks as well). This is off the cuff and untested, but should be enough to get you moving in the right direction at least:

SELECT 
    a.name AppName, 
    a.id AppId,
    d.id DevId,
    d.url_key Url,
    d.name DevName,
    CAST(agg.dev_count - 1 as VARCHAR) + ' more'
FROM 
    (
    SELECT 
        MAX(ada.developer_id) first_dev_id, 
        COUNT(ada.developer_id) dev_count, 
        app_id
    FROM
        apps_developers_assignment ada 
    WHERE 
        d.id != @excludeDevId
    GROUP BY
        app_id
    ) agg
    INNER JOIN apps a ON a.id = agg.app_id
    LEFT JOIN developers d ON agg.developer_id = d.id

Upvotes: 1

Mr Shoubs
Mr Shoubs

Reputation: 15409

If you want to use a left join, you'll need to put your condition on the join, otherwise it'll crate an inner join and the rows that have null in won't get returned... try something like this:

SELECT * FROM apps a
LEFT JOIN apps_developers_assignment ada ON a.id = ada.app_id
                                            AND ada.developerid = :devid

LEFT JOIN developers d ON ada.developer_id = d.id

WHERE a.id = :appid

Upvotes: 0

Related Questions