Question: I am trying to do a postgres update using if conditions to check if a parameter is empty before attempting to update.

The desired result would be not to update when the parameter is an ampty string.

I cannot seem to figure out the correct way to do this. My first attempt is below:

```
$stmt = $db->prepare("
  UPDATE users SET
    fname = IF(trim(:fname) = '', fname, :fname)
  WHERE username = :username");
$stmt->bindParam(':username', $username);
$stmt->bindParam(':fname', $fname);
$stmt->execute();

```

and then my second attempt was using:

```
$stmt = $db->prepare("
  UPDATE users SET fname =
    IF
      trim(:fname) =  ''
    THEN
      fname
    ELSE
      :fname
    END IF;
  WHERE username = :username
");
$stmt->bindParam(':username', $username);
$stmt->bindParam(':fname', $fname);
$stmt->execute();

```

What is the correct way of doing this with IF statements?

Answer: 
You are looking for just one matching row in each group, so you can use an aggregation function:

```
SELECT ps.id, ps.identifier, MAX(tn.name) as name
FROM pokemon_species ps JOIN
     pokemon_types pt
     ON ps.id = pt.pokemon_id JOIN
     type_names tn
     ON pt.type_id = tn.type_id
GROUP BY 1
HAVING COUNT(*) = 1
ORDER BY ps.id;

```

Presumably, `pokemon_species.id` is declared as a primary key. That is why you can leave `ps.identifier` out of the `GROUP BY`.
You can filter on the pokemon ids that are single type.

```
SELECT pokemon_species.id,
       pokemon_species.identifier,
       type_names.name
FROM pokemon_species
    JOIN pokemon_types ON pokemon_species.id = pokemon_types.pokemon_id
    JOIN type_names ON pokemon_types.type_id = type_names.type_id
WHERE pokemon_species.id IN (
   SELECT pokemon_id
   FROM pokemon_types
   GROUP BY pokemon_id 
   HAVING COUNT(*) = 1
)

```

Or you could use window functions to count the different types, and then filter

```
WITH CTE AS 
(SELECT pokemon_species.id,
       pokemon_species.identifier,
       type_names.name as type_name,
       COUNT(distinct type_names.name) OVER (PARTITION BY pokemon_species.id) as type_count
FROM pokemon_species
    JOIN pokemon_types ON pokemon_species.id = pokemon_types.pokemon_id
    JOIN type_names ON pokemon_types.type_id = type_names.type_id
)
SELECT id,
       identifier,
       type_name
FROM CTE
WHERE type_count=1

```