SQL left outer join multiple tables

Question

I have three tables and I think I need to create a left outer join.

Country:

country_id   country_name  
--------------------------
   15        United States  
   20        Sweden  

Partner:

partner_id    partner_name  
---------------------------
   1          Atlas Group  
   2          Jenkins  
   3          Roadmap  

Member:

member_id    name    registration date    partner_id    country_id  
------------------------------------------------------------------
   001       Bob     2014-01-02                1            15  
   002       Ken     2014-05-02                3            20  

I want to return a count of all members who have registered since 2014 by country name and partner name including all members who do not have a country name. (So this should include Ken with country_id of 200 which does not match to a country in the country table.

so far I have

SELECT 
    country.country_name, partner.partner_name 
FROM 
    COUNT (*) from member 
WHERE
    registration_date >= 2014-01-01

but I realize I am probably way off.

Thank you in advance for any assistance.

Answer 1

Unless I'm misunderstanding the question this is a simple LEFT JOIN of 2 tables.

SELECT country_name, partner_name, count(member_id)
FROM member
    LEFT JOIN partner USING (partner_id)
    LEFT JOIN country USING (country_id)
WHERE registration_date >= '2014-01-01'
GROUP BY partner_id, country_id

If you want to require the partner to exist then you may want to change LEFT JOIN partner ... to JOIN partner ....

You could have used the ON syntax for the JOINs but then you would have to qualify the use of partner_id and country_id elsewhere in the query.

Answer 2

something like:

select 
    a.*
    ,p.partner_name
from
    (
        select 
            m.name
            ,c.country_name
            ,partner_id
        from 
            member as m 
            left outer join country as c
                on m.country_id = c.country_id
        where 
            registrationdate >= '2014-01-01'
    ) as a
    left outer join partner as p
        on p.partner_id = a.parner_id

when using two left outer joins you need wrap the first one in a sub query if you want to get matching rows from table 3 where there was no matching row in table 2.. if i am remembering correctly.

EDIT: sorry didnt see that count part of the question!

select 
    a.country
    ,p.partner_name
    ,count(*)
from
    (
        select 
            m.name
            ,c.country_name
            ,partner_id
        from 
            member as m 
            left outer join country as c
                on m.country_id = c.country_id
        where 
            registrationdate >= '2014-01-01'
    ) as a
    left outer join partner as p
        on p.partner_id = a.parner_id
group by 
    a.country
    ,p.partner_name

but watch out as all your nulls will be counted together.. so everyone with no country assigned is in the same country.