2

I have these tables setup:
NOMINATIONS: A table of award nominations
NOMINATION_NOMINEES: A table of users with a FK on NOMINATIONS.ID

One Nomination can be referenced by many nominees via the ID field.

SELECT a.*, COUNT(SELECT all records from NOMINATION_NOMINEES with this ID) AS "b" 
FROM NOMINATIONS a
LEFT JOIN NOMINATION_NOMINEES b on a.ID = b.ID

The results would look like:

ID | NOMINATION_DESCRIPTION | ... | NUMBER_NOMINEES

Where NUMBER_NOMINEES is the number of rows in the NOMINATION_NOMINEES table with the current row's ID.

This is a tricky one, we are feeding this into a larger system so I'm hoping to get this in one query with a bunch of subqueries. Implementing subqueries into this has twisted my mind. Anyone have an idea of where to head with this?

I'm sure the above way is not close to a decent approach to this one, but I can't quite wrap my mind around this one.

vosicz
  • 59
  • 6
JamesTheMage
  • 82
  • 1
  • 8

2 Answers2

2

It can be done with a single correlated sub-query in SELECT clause. 

SELECT a.*, 
       ( SELECT COUNT(b.ID) FROM NOMINATION_NOMINEES b WHERE a.ID= b.ID )
FROM NOMINATIONS a
Community
  • 1
  • 1
PM 77-1
  • 12,933
  • 21
  • 68
  • 111
2

You should be able to use count as an analytic function:

select a.*,
       count(b.id) over (partition by b.id) 
from nominations a
       left join nomination_nominees b on a.id = b.id
sgeddes
  • 62,311
  • 6
  • 61
  • 83