CODEWITHSUNDEEP
sqloracle-database
Martin
Martin

Reputation: 1090

Can't get this query to work in SQL (Oracle)

Im trying to return the officials name, the hotel they manage and the number of matches they have refereed from the below database (see relational diagram below).

I have tried the following, but it doesn't work:

select officials.name as Official,
hotels.name as Hotel, -- hotel the official manages
count (case when officials.name = matches.referee then 1 else null end) as Matchesrefereed
from officials
left join hotels
on  officials.staffid = hotels.manager
left join matches
on officials.staffid = matches.referee
where (case when hotels.name is null then '-' else hotels.name end); -- print '-' if does not manage hotel

I get a group function error for select, and the case statement at the end also doesn't work.

Relational diagram from reference:

enter image description here

Upvotes: 0

Views: 136

Answers (1)

Kirill Leontev
Kirill Leontev

Reputation: 10931

What is your DBMS?

select 
   officials.name as Official,
   nvl(hotels.name, '-') as Hotel, -- hotel the official manages
   count (matches.referee) as Matchesrefereed
  from officials
  left join hotels on  officials.staffid = hotels.manager
  left join matches on officials.staffid = matches.referee
group by officials.name, nvl(hotels.name, '-')

Upvotes: 1

Related Questions