SQL Multiple Join but Retain all Records from One Table

RAH 0
Viewed 37

I have a difficult operation that must be performed within SQL due to operational limitations.

I have 3 tables that contain required information. They each have some common columns that can be used for a join but do not have a single one that all three share.

The 3 tables are:

Table 1:

| rule_type | code |
|-----------|------|
| Type A    | A1   |
| Type A    | A1   |
| Type B    | B1   |
| Type B    | B1   |
| Type C    | C1   |
| Type C    | C1   |

Table 2:

| site_ref | code |
|----------|------|
| XYZ      | A1   |
| XYZ      | A1   |
| XYZ      | C1   |
| XYZ      | C1   |

Table 3:

| site_ref | population |
|----------|------------|
| XYZ      | 100        |
| XYZ      | 100        |
| XYZ      | 100        |

The JOIN required must contain all 3 columns and an additional one that counts the number of distinct entries from table 1. The desired outcome would be:

| rule_type | code | site_ref | population | count |
|-----------|------|----------|------------|-------|
| Type A    | A1   | XYZ      | 100        | 2     |
| Type B    | B1   | XYZ      | 100        | 0     |
| Type C    | C1   | XYZ      | 100        | 2     |

I have attempted to create this joining on common columns via a FULL OUTER JOIN:

SELECT code, count(*) as count, site_ref, population, rule_type, population
FROM 
  (SELECT A.code, count(*) as count, B.site_ref, C.population, A.rule_type
   FROM table_1 as A
     FULL OUTER JOIN table_2 AS B ON A.code = B.code
     JOIN table_3 as C ON B.site_ref = C.site_ref
   WHERE site_ref = 'XYZ' AND rule_type in ('Type A', 'Type B', 'Type C')) 
GROUP BY code, count(*) as count, site_ref, population, rule_type, population

But this is returning:

| rule_type | code | site_ref | population | count |
|-----------|------|----------|------------|-------|
| Type A    | A1   | XYZ      | 100        | 2     |
| Type C    | C1   | XYZ      | 100        | 2     |

As because there is no corresponding Type B count in table 2, it has nothing to count. I thought using a FULL OUTER JOIN would bring in these additional rule types but it hasn't. Is there a way to adapt the JOIN that will bring in the additional columns from table 1 and create an entry showing the columns of Type B but with a count of 0?

outer-join join sql
1 Answers

i dont understand the table 3, they have always the same data, if not how we know which dataset is for the type B? If you want to have all possibilities, you can do a cross join.

with table_1 
as
(
Select 'Type A' as rule_type, 'A1' as code
Union all
Select 'Type A' as rule_type, 'A1' as code
Union all
Select 'Type B' as rule_type, 'B1' as code
Union all
Select 'Type B' as rule_type, 'B1' as code
Union all
Select 'Type C' as rule_type, 'C1' as code
Union all
Select 'Type C' as rule_type, 'C1' as code
),
table_2
as
(
Select 'XYZ' as site_ref, 'A1' as code
Union all
Select 'XYZ' as site_ref, 'A1' as code
Union all
Select 'XYZ' as site_ref, 'C1' as code
Union all
Select 'XYZ' as site_ref, 'C1' as code
),
table_3
as
(
Select 'XYZ' as site_ref, '100' as population
Union all
Select 'XYZ' as site_ref, '100' as population
Union all
Select 'XYZ' as site_ref, '100' as population
)
Select 
        x.code,
        x.rule_type,
        c.site_ref,
        c.population,
        x.count
from    
    (
        Select 
            a.code,
            a.rule_type,
            b.count
        from(
            Select code,
            count(code) as count
            from table_2
            group by code
        ) B
        full outer join  table_1 AS A ON 
        a.code = b.code
        group by 
            a.code,
            a.rule_type,
            b.count
    ) x
cross join table_3 as c
WHERE site_ref = 'XYZ' AND rule_type in ('Type A', 'Type B', 'Type C') 
group by 
        x.code,
        x.rule_type,
        c.site_ref,
        c.population,
        x.count

if you want default values, you can use case when

Select 
    a.code,
    a.rule_type,
    case when x.site_ref is NUll then 'XYZ' else x.site_ref end as site_ref ,
    case when c.population is NUll then '100' else c.population end as population,
    case when x.count is NUll then '0' else x.count end as count
from
    (
        Select
            code,
            site_ref, 
            count(b.Code) as count
        from 
            table_2 as b
        group by 
            b.code,
            b.site_ref
    ) x 
full outer  join table_1 as a on 
    a.code = x.code
full outer join table_3 as c ON
x.site_ref = c.site_ref
group by 
    a.code,
    a.rule_type,
    x.site_ref,
    c.population,
    x.count
user199506270
Related