title: postgresql self join
body: <p>Say I have a table like so</p>
  
  <pre><code>  id  |     device     |  cmd  | value | 
  ------+----------------+-------+---------
  
  id = unique row ID
  device = device identifier (mac address)
  cmd = some arbitrary command
  value = value of corresponding command
  </code></pre>
  
  <p>I would like to somehow self join on this table to grab specific cmds and their corresponding values for a particular device.</p>
  
  <p>I do not want just <code>SELECT cmd,value FROM table WHERE device='00:11:22:33:44:55';</code></p>
  
  <p>Say the values I want correspond to the <code>getname</code> and <code>getlocation</code> commands.  I would like to have output something like</p>
  
  <pre><code>        mac         |    name   | location
  --------------------+-----------+------------
   00:11:22:33:44:55  | some name | somewhere
  </code></pre>
  
  <p>My sql fu is pretty pants.  I've been trying different combinations like <code>SELECT a.value,b.value FROM table AS a INNER JOIN table AS b ON a.device=b.device</code> but I am getting nowhere.</p>
  
  <p>Thanks for any help.</p>