I have three different tables that look like that:

Table 1

| id | city|
|----|-----|
| 1  |  A  |
| 1  |  B  |
| 2  |  C  | 

Table 2

| id | city|
|----|-----|
| 2  |  B  |
| 1  |  B  |
| 3  |  C  |

Table 3

| id | city|
|----|-----|
| 1  |  A  |
| 1  |  B  |
| 2  |  A  |

I need to create one column for each table, and the dummies values if it's present.

| id | city|  is_tbl_1 |  is_tbl_2   | is_tbl_3   |
|----|-----|-----------|-------------|------------|
| 1  |  A  |    1      |      0      |     1      |
| 1  |  B  |    1      |      1      |     1      |
| 2  |  A  |    0      |      0      |     1      |
| 2  |  C  |    1      |      0      |     0      |
| 2  |  B  |    0      |      1      |     0      |
| 3  |  C  |    0      |      1      |     0      |

I have tried to add the columns is_tbl# myself on three different selects, UNION all the three tables and group, but it looks ugly, is there a better way to do it?

CodePudding user response：

You can outer-join the 3 tables on id and city, then group by the id and city, and finally count the number of non-null values of the city columns :

SELECT 
    COALESCE (t1.id, t2.id, t3.id) AS id
    , COALESCE (t1.city, t2.city, t3.city) AS city
    , count(*) FILTER (WHERE t1.city IS NOT NULL) AS is_tbl_1
    , count(*) FILTER (WHERE t2.city IS NOT NULL) AS is_tbl_2
    , count(*) FILTER (WHERE t3.city IS NOT NULL) AS is_tbl_3
FROM
    t1 AS t1
FULL OUTER JOIN 
    t2 AS t2 ON t1.id = t2.id AND t1.city = t2.city
FULL OUTER JOIN 
    t3 AS t3 ON t1.id = t3.id AND t1.city = t3.city
GROUP BY 
    1,2
ORDER BY 
    1,2