SQL Conditioning

Question

I want to show department name and number of departments outside Australia.

The problem is this code below still gives a count including departments in Australia.

(edit)- this is closer to the solution but still not quite right. - not returning correct count.

SELECT department_name, COUNT(COUNTRIES.country_id)
FROM DEPARTMENTS, COUNTRIES
JOIN (
     SELECT COUNT(COUNTRIES.country_name) AS COUNTRY
     FROM COUNTRIES
     JOIN DEPT_LOCATIONS ON COUNTRIES.country_id = DEPT_LOCATIONS.country_id
     WHERE country_name <> 'Australia'
     ) AS C ON COUNTRY = c.country
     GROUP BY department_name;

Here is the database schema:

COUNTRIES(country_id, country_name, region) 
• Primary key: {country_id}

DEPARTMENTS(department_id, department_name, manager_id)
• Primary key: {department_id }
• Foreign key: [manager_id]  ⊆ EMPLOYEES[employee_id]

DEPT_LOCATIONS(department_id, city, country_id)
• Primary key: {department_id, city}
• Foreign key: [department_id]  ⊆ DEPARTMENTS[department_id] and 
[country_id]  ⊆ COUNTRIES[country_id]

EMPLOYEES(employee_id, firstname, lastname, email, phone_no, salary, 
department_id)
• Primary key: {employee_id}
• Foreign key: [department_id]  ⊆ DEPARTMENTS[department_id]

Answer 1

Figured it out myself cheers

SELECT department_name, COUNT(COUNTRIES.country_id)
FROM DEPARTMENTS, COUNTRIES
RIGHT JOIN DEPT_LOCATIONS ON DEPT_LOCATIONS.country_id = COUNTRIES.country_id
WHERE DEPARTMENTS.department_id = DEPT_LOCATIONS.department_id
AND country_name <> 'Australia'
GROUP BY department_name;

Answer 2

Have you tried this

SELECT department_name, COUNT(DEPT_LOCATIONS.country_id)
FROM DEPARTMENTS
LEFT JOIN DEPT_LOCATIONS ON DEPARTMENTS.department_id = DEPT_LOCATIONS.department_id       
GROUP BY department_name
HAVING department_name <> 'Australia'