Question: 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.

Answer: 
I'm not sure if the proof of concept example does what you want. In general, with SQL, you almost *never* need a FOR loop. While you can use a function, if you have PostgreSQL 9.3 or later, you can use a [`LATERAL` subquery](http://www.postgresql.org/docs/current/static/queries-table-expressions.html) to perform subqueries for each row.

For example, create 10,000 random 3D points with a random `value` column:

```
CREATE TABLE points(
  gid serial primary key,
  geom geometry(PointZ),
  value numeric
);
CREATE INDEX points_geom_gist ON points USING gist (geom);
INSERT INTO points(geom, value)
SELECT ST_SetSRID(ST_MakePoint(random()*1000, random()*1000, random()*100), 0), random()
FROM generate_series(1, 10000);

```

For each point, search for the 100 nearest points (except the point in question), and find the residual between the points' `value` and the average of the 100 nearest:

```
SELECT p.gid, p.value - avg(l.value) residual
FROM points p,
  LATERAL (
    SELECT value
    FROM points j
    WHERE j.gid <> p.gid
    ORDER BY p.geom <-> j.geom
    LIMIT 100
) l
GROUP BY p.gid
ORDER BY p.gid;

```
Following is a simple example to update rows in a table:

Assuming the row id field `id`

Update all rows:

```
UPDATE my_table SET field1='some value'
WHERE id IN (SELECT id FROM staff)

```

Selective row update

```
UPDATE my_table SET field1='some value'
WHERE id IN (SELECT id FROM staff WHERE field2='same value')

```