CODEWITHSUNDEEP
sqloracle-database
Jake Cube
Jake Cube

Reputation: 143

SQL, Inner join issue in oracle

enter image description here

^ HOSPITAL_SPECIALISE_TEST ^

enter image description here ^ HOSPITAL ^

Here are my 2 tables. I'm trying to use inner join to generate a result and this is the query I currently use : 

SELECT HOSPITAL_ID, COUNT(DISTINCT SPECIALISE) AS SPECIALISE FROM HOSPITAL_SPECIALISE_TEST GROUP BY HOSPITAL_ID;

And I get the result below...

enter image description here

Now here is the question, I want to join both tables and display hospital name, but it fails to generate and I don't know why... Here is my query: 

SELECT HS.HOSPITAL_ID, HP.NAME, COUNT(DISTINCT HS.SPECIALISE) AS SPECIALISE 
FROM HOSPITAL_SPECIALISE_TEST HS
INNER JOIN HOSPITAL HP 
on HS.HOSPITAL_ID = HP.HOSPITAL_ID 
GROUP BY HOSPITAL_ID;

Upvotes: 0

Views: 173

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Although the hospital name can be unambiguously concluded from the hospital ID you are grouping by, Oracle demands it to be either also included in the GROUP by clause or be aggregated. This leads to one of these rather clumsy queries:

select hs.hospital_id, hp.name, count(distinct hs.specialise) as specialise 
from hospital_specialise_test hs
inner join hospital hp on hs.hospital_id = hp.hospital_id 
group by hs.hospital_id, hp.name;

or

select hs.hospital_id, max(hp.name) as name, count(distinct hs.specialise) as specialise 
from hospital_specialise_test hs
inner join hospital hp on hs.hospital_id = hp.hospital_id 
group by hs.hospital_id;

This is compliant with the old SQL 92 standard, but not with newer versions of the SQL standard which would happily accept your query :-)

You can circumvent the issue by not joining the tables:

select 
  hs.hospital_id, 
  (select name from hospital hp where hs.hospital_id = hp.hospital_id) as name,
  count(distinct hs.specialise) as specialise 
from hospital_specialise_test hs
group by hospital_id, hp.name;

or by joining pre-aggregated records, as suggested by cableload.

Upvotes: 1

T Gray
T Gray

Reputation: 712

The group by must contain all select columns (other than the aggregate):

GROUP BY HS.HOSPITAL_ID, HP.NAME.

Upvotes: 0

cableload
cableload

Reputation: 4375

Do this instead

SELECT HP.NAME, spec.hospital_id, spec.specialise
FROM (  SELECT HOSPITAL_ID, COUNT (DISTINCT SPECIALISE) AS SPECIALISE
        FROM HOSPITAL_SPECIALISE_TEST
    GROUP BY HOSPITAL_ID) spec
   INNER JOIN HOSPITAL HP ON spec.HOSPITAL_ID = HP.HOSPITAL_ID

Upvotes: 1

Related Questions