postgresql self join

Question

Say I have a table like so

  id  |     device     |  cmd  | value | 
------+----------------+-------+---------

id = unique row ID
device = device identifier (mac address)
cmd = some arbitrary command
value = value of corresponding command

I would like to somehow self join on this table to grab specific cmds and their corresponding values for a particular device.

I do not want just SELECT cmd,value FROM table WHERE device='00:11:22:33:44:55';

Say the values I want correspond to the getname and getlocation commands. I would like to have output something like

        mac         |    name   | location
--------------------+-----------+------------
 00:11:22:33:44:55  | some name | somewhere

My sql fu is pretty pants. I've been trying different combinations like SELECT a.value,b.value FROM table AS a INNER JOIN table AS b ON a.device=b.device but I am getting nowhere.

Thanks for any help.

Answer 1

SELECT a.value AS thisval ,b.value AS thatval
FROM table AS a JOIN table AS b USING (device)
WHERE a.command='this' AND b.command='that';