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.

Michael Krelin - hacker · Accepted Answer · 2011-04-13 11:16:33Z

11

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

answered Apr 13, 2011 at 11:16

Michael Krelin - hacker

144k25 gold badges200 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

tbh1 Over a year ago

Thanks for that. I suppose if I wanted four values instead of two, I'd need three JOIN's?

Michael Krelin - hacker Over a year ago

Yes, but maybe you want to reconsider your database structure if this is important ;-)

Michael Krelin - hacker Over a year ago

Another possibility would be to use group by and sort them out in select list.

Collectives™ on Stack Overflow

postgresql self join

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related