How do I create a query to count the number of times a foreign key is used in another table?

Question

I have two tables:

CREATE TABLE  "OEHR_COUNTRIES" (
    "COUNTRY_ID"    CHAR(2)     CONSTRAINT "OEHR_COUNTRY_ID_NN" NOT NULL ENABLE, 
    "COUNTRY_NAME"  VARCHAR2(40), 
    "REGION_ID"     NUMBER, 
    CONSTRAINT "OEHR_COUNTRY_C_ID_PK"   PRIMARY KEY ("COUNTRY_ID") ENABLE
   )/

CREATE TABLE  "OEHR_LOCATIONS" (
    "LOCATION_ID"       NUMBER(4,0), 
    "STREET_ADDRESS"    VARCHAR2(40), 
    "POSTAL_CODE"       VARCHAR2(12), 
    "CITY"              VARCHAR2(30) CONSTRAINT "OEHR_LOC_CITY_NN" NOT NULL ENABLE, 
    "STATE_PROVINCE"    VARCHAR2(25), 
    "COUNTRY_ID"        CHAR(2), 
     CONSTRAINT "OEHR_LOC_ID_PK" PRIMARY KEY ("LOCATION_ID") ENABLE
   ) 
/

and i need to create a query that display's the name and id of every country along with the number of locations that each has.

OK, what have you attempted? – OldProgrammer Mar 02 '17 at 18:23 — OldProgrammer, Mar 02 '17 at 18:23
@EJG101 - Please don't vandalize the post for no reason. – Gurwinder Singh Mar 03 '17 at 16:24 — Gurwinder Singh, Mar 03 '17 at 16:24

score 0 · Answer 1 · answered Mar 02 '17 at 18:25

0

It's a simple left join and aggregate:

select c.country_id,
    c.country_name,
    count(*) num_of_locations
from oehr_countries c
left join oehr_locations l on c.country_id = l.country_id
group by c.country_id,
    c.country_name

answered Mar 02 '17 at 18:25

Gurwinder Singh

38,557
6
51
76

score 0 · Answer 2 · answered Mar 02 '17 at 18:26

0

select country_name, count(*)
from oehr_countries m, oehr_locations d
where m.country_id = d.country_id

answered Mar 02 '17 at 18:26

Randy Brown

11
3

This will omit countries without a location - using an outer join is safer. Also, old-style join syntax (multiple tables in the FROM clause instead of explicit JOINs) should no longer be used. – Frank Schmitt Mar 02 '17 at 18:57
Sorry, I am new here. Everybody keeps saying that ANSI join syntax is the only way to go, but nobody ever says why. I'm probably going to wait until I hear it from Mr Ellison or one of his minions. However, if you can cite a convincing reference, I might try to read it. – Randy Brown Mar 05 '17 at 18:09
I am not convinced that omitting countries without a location makes any difference. If the goal is to count foreign keys, then we are bypassing rows which contain no relevant information. – Randy Brown Mar 05 '17 at 18:11
http://stackoverflow.com/a/1599201/610979 cites a number of reasons (e.g avoiding accidental cross joins, ambiguity of old-style syntax in certain cases). On top of that, the old-style syntax makes it impossible to write a full outer join in a clean way. Also see http://www.orafaq.com/node/2618. And if that's not enough, let's quote the Oracle docs: "Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. " (see http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm#SQLRF52337) :-) – Frank Schmitt Mar 05 '17 at 21:07
With respect, an opinion published on stackoverflow will not convince me. I do not believe the argument posted on orafaq because I do not believe his assertion that Oracle syntax necessarily forces a Cartesian join. The final link applies specifically to outer joins. Eventually, ANSI syntax may take over the Oracle world. I don't think we have reached that point yet. – Randy Brown Mar 05 '17 at 21:39
Fair enough. I switched from old-style syntax to ANSI syntax several years ago, and I never regretted it - it feels so much cleaner, esp. if you write a lot of outer joins in your daily queries (which I do). But YMMV. – Frank Schmitt Mar 05 '17 at 21:50

How do I create a query to count the number of times a foreign key is used in another table?

2 Answers2