title: How to create an "on-the-fly" mapping table within a SELECT statement in Postgresql
body: <p>I'm creating a select statement that combines two tables, <code>zone</code> and <code>output</code>, 
  based on a referenced <code>device</code> table and on a mapping of <code>zone_number</code> to <code>output_type_id</code>. 
  The mapping of <code>zone_number</code> to <code>output_type_id</code> doesn't appear
  anywhere in the database, and I would like to create it "on-the-fly" within the select
  statement. Below is my schema: </p>
  
  <pre><code>CREATE TABLE output_type (
      id INTEGER NOT NULL, 
      name TEXT,
      PRIMARY KEY (id)
  );
  
  CREATE TABLE device (
      id INTEGER NOT NULL,
      name TEXT,
      PRIMARY KEY (id)
  );
  
  CREATE TABLE zone (
      id SERIAL NOT NULL,
      device_id INTEGER NOT NULL REFERENCES device(id),
      zone_number INTEGER NOT NULL,
      PRIMARY KEY (id), 
      UNIQUE (zone_number)
  );
  
  CREATE TABLE output (
      id SERIAL NOT NULL,
      device_id INTEGER NOT NULL REFERENCES device(id),
      output_type_id INTEGER NOT NULL REFERENCES output_type(id),
      enabled BOOLEAN NOT NULL,
      PRIMARY KEY (id)
  );
  </code></pre>
  
  <p>And here is some example data:</p>
  
  <pre><code>INSERT INTO output_type (id, name) VALUES 
  (101, 'Output 1'),
  (202, 'Output 2'),
  (303, 'Output 3'),
  (404, 'Output 4');
  
  INSERT INTO device (id, name) VALUES 
  (1, 'Test Device');
  
  INSERT INTO zone (device_id, zone_number) VALUES 
  (1, 1),
  (1, 2),
  (1, 3),
  (1, 4);
  
  INSERT INTO output (device_id, output_type_id, enabled) VALUES 
  (1, 101, TRUE),
  (1, 202, FALSE),
  (1, 303, FALSE), 
  (1, 404, TRUE);
  </code></pre>
  
  <p>I need to get the associated <code>enabled</code> field from the output table for each zone for a given device. 
  Each <code>zone_number</code> maps to an <code>output_type_id</code>. For this example: </p>
  
  <pre><code>zone_number | output_type_id
  ----------------------------
  1           | 101
  2           | 202
  3           | 303 
  4           | 404
  </code></pre>
  
  <p>One way to handle the mapping would be to create a new table</p>
  
  <pre><code>CREATE TABLE zone_output_type_map (
      zone_number INTEGER,
      output_type_id INTEGER NOT NULL REFERENCES output_type(id)
  );
  
  INSERT INTO zone_output_type_map (zone_number, output_type_id) VALUES 
  (1, 101),
  (2, 202),
  (3, 303), 
  (4, 404);
  </code></pre>
  
  <p>And use the following SQL to get all zones, plus the <code>enabled</code> flag, for device 1: </p>
  
  <pre><code>SELECT zone.*, output.enabled 
  FROM zone
  JOIN output 
  ON output.device_id = zone.device_id
  JOIN zone_output_type_map map
  ON map.zone_number = zone.zone_number
  AND map.output_type_id = output.output_type_id
  AND zone.device_id = 1
  </code></pre>
  
  <p>However, I'm looking for a way to create the mapping of zone nunbers to output
  types without creating a new table and without piecing together a bunch of AND/OR
  statements. Is there an elegant way to create a mapping between the two fields 
  within the select statement? Something like: </p>
  
  <pre><code>SELECT zone.*, output.enabled 
  FROM zone
  JOIN output 
  ON output.device_id = zone.device_id
  JOIN (
      SELECT (
          1 =&gt; 101,
          2 =&gt; 202,
          3 =&gt; 303,
          4 =&gt; 404
      ) (zone_number, output_type_id)
  ) as map
  ON map.zone_number = zone.zone_number
  AND map.output_type_id = output.output_type_id
  AND zone.device_id = 1
  </code></pre>
  
  <p>Disclaimer: I know that ideally the <code>enabled</code> field would exist in the <code>zone</code> 
  table. However, I don't have control over that piece. I'm just looking for the 
  most elegant solution from the application side. Thanks!</p>