How to join multiple table to one central table [SQl]-CodePudding

So I'm learning SQL and encountered a problem: I want to join multiple table to one central table and get back all unique results from each join split into columns. I am gonna illustrate it for easier understanding. ! I have 1 central table and 3 outer tables that are not connected to one another](https://i.stack.imgur.com/dzbjw.jpg)

In the output table that should look like that: ! the desired format](https://i.stack.imgur.com/hsdBB.png)

However, I cannot do it with a simple Join statement - as there is no data common for all the tables, the return value is No Data. When I run just a single join statement with the center table and one of the outer tables it works just fine of course. I am also gonna show you the code right here for reference:

WITH active_campaign AS (SELECT fr.campaign_id, DATE_TRUNC(fr.day, MONTH) AS month, SUM(fr.imps_count) AS sum_of_impressions, advertiser_id 
                                        FROM `some_table` AS fr
                                        WHERE day between (CURRENT_DATE() - 365) AND (CURRENT_DATE() - EXTRACT(DAY FROM CURRENT_DATE())  1)
                                        GROUP BY fr.campaign_id, month, advertiser_id 
                                        HAVING SUM(fr.imps_count) > 100
                                        ORDER BY month desc),

PC_JPN AS (SELECT id, profitCenter
FROM some_table2 AS camps
WHERE camps.profitCenter IN ('JPN')),

PC_A AS (SELECT id, profitCenter
FROM some_table2 AS camps
WHERE camps.profitCenter IN ('A')),

PC_B AS (SELECT id, profitCenter
FROM some_table2 AS camps
WHERE camps.profitCenter IN ('B')),

PC_C AS (SELECT id, profitCenter
FROM some_table2 AS camps
WHERE camps.profitCenter IN ('C'))



SELECT active.month, COUNT(DISTINCT pc_jpn.id) AS JPN
FROM active_campaign AS active
JOIN PC_JPN AS pc_jpn
ON active.advertiser_id = pc_jpn.id 
GROUP BY active.month
ORDER BY active.month DESC

So as you can see I have those 5 tables: active_campaign, JPN, A, B and C and I need JPN, A, B and C to display next to each other as distint columns. Is that even possible?

Will be very grateful for help

Tried different join statements, subqueries but nothing worked, also tried researching ofc but couldn't find anything relevant :(

CodePudding user response：

Not sure I understand your question completely, but I think you want something like the following:

SELECT 
  active.month, 
  COUNT(DISTINCT pc_jpn.id) AS JPN,
  COUNT(DISTINCT pc_a.id) AS A,
  COUNT(DISTINCT pc_b.id) AS B,
  COUNT(DISTINCT pc_c.id) AS C

FROM active_campaign AS active

LEFT JOIN PC_JPN AS pc_jpn
  ON active.advertiser_id = pc_jpn.id 

LEFT JOIN PC_A AS pc_a
  ON active.advertiser_id = pc_a.id 

LEFT JOIN PC_B AS pc_b
  ON active.advertiser_id = pc_b.id 

LEFT JOIN PC_C AS pc_c
  ON active.advertiser_id = pc_c.id 


GROUP BY active.month
ORDER BY active.month DESC