SQL- how would i Join these two queries

Question

I have the following queries, and am attempting to join them

SELECT COUNTRY_NAME, COUNTRY_ID
FROM OEHR_COUNTRIES;

these results

COUNTRY_NAME                             CO                                                         
---------------------------------------- --                                                         
Argentina                                AR                                                         
Australia                                AU                                                         
Belgium                                  BE                                                         
Brazil                                   BR                                                         
Canada                                   CA                                                         
Switzerland                              CH                                                         
China                                    CN                                                         
Germany                                  DE                                                         
Denmark                                  DK                                                         
Egypt                                    EG                                                         
France                                   FR                                                         
HongKong                                 HK                                                         
Israel                                   IL                                                         
India                                    IN                                                         
Italy                                    IT                                                         
Japan                                    JP                                                                                                                 
Kuwait                                   KW                                                         
Mexico                                   MX                                                         
Nigeria                                  NG                                                         
Netherlands                              NL                                                         
Singapore                                SG                                                         
United Kingdom                           UK                                                         
United States of America                 US                                                         
Zambia                                   ZM                                                         
Zimbabwe                                 ZW 

my second query

SELECT COUNTRY_ID,  COUNT(COUNTRY_ID) AS "LCOUNT"
FROM OEHR_LOCATIONS
GROUP BY COUNTRY_ID;

results

CO  LCOUNT                                                                                          
-- -------                                                                                          
US       4                                                                                          
SG       1                                                                                          
CA       2                                                                                          
CH       2                                                                                          
IT       2                                                                                          
MX       1                                                                                          
CN       1                                                                                          
DE       1                                                                                          
JP       2                                                                                          
IN       1                                                                                          
AU       1                                                                                          
UK       3                                                                                          
BR       1                                                                                          
NL       1  

When i attempt to join these two results, so each country has the count after it

SELECT OEHR_COUNTRIES.COUNTRY_NAME, OEHR_COUNTRIES.COUNTRY_ID, COUNT(OEHR_LOCATIONS.COUNTRY_ID) AS LCOUNT
FROM OEHR_COUNTRIES
OUTER JOIN OEHR_LOCATIONS
ON OEHR_COUNTRIES.COUNTRY_ID = OEHR_LOCATIONS.COUNTRY_ID
ORDER BY LCOUNT;

i get this error

    ON OEHR_COUNTRIES.COUNTRY_ID = OEHR_LOCATIONS.COUNTRY_ID
   *
ERROR at line 4:
ORA-00904: "OEHR_COUNTRIES"."COUNTRY_ID": invalid identifier 


ON OEHR_COUNTRIES.COUNTRY_ID = OEHR_LOCATIONS.COUNTRY_ID
   *
ERROR at line 4:
ORA-00904: "OEHR_COUNTRIES"."COUNTRY_ID": invalid identifier

what is causing this error?

is there a simpler way to do what i am trying to achieve?

Answer 1

I assume this is something you need. It would list 0 for countries with no count. If you dont want to list countries with no count, use INNER JOIN

SELECT C.COUNTRY_NAME, 
case 
when L.LCOUNT is null 
    then 0
    else L.LCOUNT 
END  as LCOUNT
FROM OEHR_COUNTRIES C
LEFT JOIN 
(SELECT COUNTRY_ID,  COUNT(COUNTRY_ID) AS LCOUNT
FROM OEHR_LOCATIONS
GROUP BY COUNTRY_ID) L
on C.COUNTRY_ID=L.COUNTRY_ID
order by LCOUNT DESC

Answer 2

You're missing the mandatory LEFT (or, in other scenarios, RIGHT) before the optional OUTER in the join syntax.

At the moment the word OUTER is being misinterpreted as a table alias, which is what is causing the error you're getting - there is, to the parser, now an OUTER.COUNTRY_ID but not a OEHR_COUNTRIES.COUNTRY_ID.

Add the missing word to stop it being seen as an alias, and to stop it defaulting to an inner join:

SELECT OEHR_COUNTRIES.COUNTRY_NAME, OEHR_COUNTRIES.COUNTRY_ID,
 COUNT(OEHR_LOCATIONS.COUNTRY_ID) AS LCOUNT
FROM OEHR_COUNTRIES
LEFT OUTER JOIN OEHR_LOCATIONS
ON OEHR_COUNTRIES.COUNTRY_ID = OEHR_LOCATIONS.COUNTRY_ID
GROUP BY OEHR_COUNTRIES.COUNTRY_NAME, OEHR_COUNTRIES.COUNTRY_ID
ORDER BY LCOUNT;

I've added the missing group-by clause too. With your sample data that gets:

COUNTRY_NAME             CO     LCOUNT
------------------------ -- ----------
Belgium                  BE          0
Argentina                AR          0
Zimbabwe                 ZW          0
...
Zambia                   ZM          0
Mexico                   MX          1
China                    CN          1
...
Germany                  DE          1
Switzerland              CH          2
Canada                   CA          2
Japan                    JP          2
Italy                    IT          2
United Kingdom           UK          3
United States of America US          4

25 rows selected. 

---

Without adding that missing word, changing the other references to the table to use the (wrong) OUTER alias instead would have meant it would execute, again with the group-by clause added:

SELECT OUTER.COUNTRY_NAME, OUTER.COUNTRY_ID, COUNT(OEHR_LOCATIONS.COUNTRY_ID) AS LCOUNT
FROM OEHR_COUNTRIES
OUTER JOIN OEHR_LOCATIONS
ON OUTER.COUNTRY_ID = OEHR_LOCATIONS.COUNTRY_ID
GROUP BY OUTER.COUNTRY_NAME, OUTER.COUNTRY_ID
ORDER BY LCOUNT;

but it wouldn't have done quite what you wanted - assuming you want to see zero counts for countries with no locations - since it's now an inner join:

COUNTRY_NAME             CO     LCOUNT
------------------------ -- ----------
Netherlands              NL          1
India                    IN          1
...
Australia                AU          1
Switzerland              CH          2
Japan                    JP          2
Canada                   CA          2
Italy                    IT          2
United Kingdom           UK          3
United States of America US          4

14 rows selected. 

The 11 countries with no locations aren't shown at all with an inner join.