Dnia ?roda, 19 stycznia 2005 15:54, Kristian Eide napisa?:
SELECT DISTINCT module.netboxid,vlanid,vlan.vlan,sysname,gwport.to_netboxid,gwport.to_swpor tid ,trunk,substr(hexstring,0,3) FROM prefix JOIN vlan USING(vlanid) JOIN gwportprefix ON (prefix.prefixid = gwportprefix.prefixid AND (hsrp='t' OR gwip::text IN (SELECT MIN(gwip::text) FROM gwportprefix GROUP BY prefixid HAVING COUNT(DISTINCT hsrp) = 1))) JOIN gwport USING(gwportid) JOIN module USING(moduleid) JOIN netbox USING (netboxid) LEFT JOIN swport ON (gwport.to_swportid=swportid) LEFT JOIN swportallowedvlan USING (swportid) ORDER BY vlan.vlan;
wow :)
netboxid | vlanid | vlan | sysname | to_netboxid | to_swportid | trunk | substr ----------+--------+------+-----------+-------------+-------------+-------+-------- 3 | 84 | 103 | CR2_pr | | | | 3 | 88 | 104 | CR2_pr | | | | 3 | 81 | 105 | CR2_pr | | | | 3 | 87 | 106 | CR2_pr | | | | 3 | 83 | 107 | CR2_pr | | | | 3 | 79 | 108 | CR2_pr | | | | 3 | 85 | 151 | CR2_pr | | | | 3 | 78 | 152 | CR2_pr | | | | 3 | 82 | 153 | CR2_pr | | | | 3 | 86 | 170 | CR2_pr | | | | 4 | 9 | 200 | CR1_p | | | | 3 | 80 | 201 | CR2_pr | | | | 4 | 17 | 250 | CR1_p | | | | 3 | 36 | | CR2_pr | | | | 3 | 45 | | CR2_pr | | | | 3 | 46 | | CR2_pr | | | | 3 | 47 | | CR2_pr | | | | 4 | 2 | | CR1_p | | | | 4 | 3 | | CR1_p | | | | 4 | 4 | | CR1_p | | | | 4 | 5 | | CR1_p | | | | 4 | 6 | | CR1_p | | | | 4 | 8 | | CR1_p | | | | 4 | 16 | | CR1_p | | | | 4 | 18 | | CR1_p | | | | 4 | 19 | | CR1_p | | | | 4 | 20 | | CR1_p | | | | 4 | 22 | | CR1_p | | | | 4 | 71 | | CR1_p | | | | 4 | 72 | | CR1_p | | | | 4 | 73 | | CR1_p | | | | 4 | 74 | | CR1_p | | | | 4 | 76 | | CR1_p | | | | 5 | 20 | | TR2_pr | | | | 5 | 21 | | TR2_pr | | | | 5 | 41 | | TR2_pr | | | | 5 | 50 | | TR2_pr | | | | 6 | 57 | | TR1_p | 1 | 22 | t | 00 6 | 58 | | TR1_p | | | | 6 | 59 | | TR1_p | | | | 6 | 60 | | TR1_p | | | | 6 | 61 | | TR1_p | | | | 6 | 62 | | TR1_p | | | | 6 | 63 | | TR1_p | | | | 6 | 64 | | TR1_p | | | | 6 | 65 | | TR1_p | | | | 6 | 66 | | TR1_p | | | | 6 | 67 | | TR1_p | | | | 6 | 68 | | TR1_p | | | | 6 | 69 | | TR1_p | | | | 6 | 70 | | TR1_p | | | | 6 | 75 | | TR1_p | | | | (52 rows)