PGSQL - Translate query results with foreign keys

Question

I have two tables that describe items and products:

Category:

| categoryid   | categoryname     |
| -------------| -----------------|
| 1            | "a"              |
| 2            | "b"              |

Item:

| itemid   | itemname      |
| -------- | --------------|
| 1        | "c"           |
| 2        | "d"           |
| 3        | "e"           |

and a third table to map them together:

ItemToCategory:

| itemid   | categoryid     |
| -------- | -------------- |
| 1        | 1              |
| 2        | 2              |
| 3        | 2              |

What's the best way to create a query that returns the itemname and categoryname of all itemnames in array '[c,d]':

| itemname | categoryname  |
| -------- | --------------|
| "c"      | "a"           |
| "d"      | "b"           |

Answer 1

You can use the ANY operator in a WHERE clause to achieve this:

select i.itemname, c.categoryname 
from item i
  join itemtocategory ic on ic.itemid = i.itemid
  join category c on c.categoryid = ic.categoryid
where i.itemname = any(array['c', 'd'])  

The condition = any(array['c', 'd']) is equivalent to IN ('c', 'd')