Posts tagged ‘sql’

27 September, 2011

Tuesday Tidbit – JOIN on multiple fields

by gorthx

I use this so rarely I have a hard time remembering the syntax; I always try to use a comma instead of AND. Which, of course, throws an error.

Looks like this:
SELECT [stuff]
FROM table1 t1
JOIN table2 t2 ON
(t1.field1 = t2.field1 AND t1.field2 = t2.field2);

Tags: ,
3 April, 2009

Friday Happy Hour: Gimme some sugar, baby.

by gorthx

Time for some more fun with managing user data, of the “who was connected where and when” type. I’m going to use PostgreSQL row constructors & subqueries to filter my data.

I have a table that contains switch names & ports which are connected to other switches:
testytest=# SELECT switch_name, switch_port, connected_to
FROM switch_connections;
switch_name | switch_port | connected_to
switch-1 | 1 | switch-2
switch-1 | 2 | switch-3
switch-2 | 1 | switch-1
switch-3 | 1 | switch-1
(4 rows)

Another table contains hostnames found on each switch port at a given point in time:

read more »