Postgresql self join

Question

Say I have a table like

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 join this table in order to capture certain cmds and their corresponding values ​​for a specific device.

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

Say that the values ​​I want correspond to the getlocation and getlocation . I would like to get something like

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

My sql fu is pretty pants. I tried different combinations, such as SELECT a.value,b.value FROM table AS a INNER JOIN table AS b ON a.device=b.device , but I'm not going anywhere.

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';