Ok, ogarnalem to tak:
mysql> select nodes.name,nodes.ipaddr,nodes.passwd,tariffs.upceil,tariffs.downceil from nodes left join nodeassignments on nodes.id = nodeassignments.nodeid left join assignments on nodeassignments.assignmentid = assignments.id left join tariffs on assignments.tariffid = tariffs.id where nodes.name = 01; +------+------------+--------+--------+----------+ | name | ipaddr | passwd | upceil | downceil | +------+------------+--------+--------+----------+ | 01 | 3252324258 | | 1216 | 4288 | | 01 | 3252324258 | | 12288 | 12288 | | 01 | 3252324258 | | 0 | 0 | +------+------------+--------+--------+----------+ 3 rows in set (0.00 sec)
mysql>
Czyli mam wynik dla t-scripta, i zrobilem tak:
{result = SELECT LOWER(nodes.name) AS name, inet_ntoa(nodes.ipaddr) AS ip , nodes.passwd AS passwd, tariffs.downceil AS down, tariffs.upceil AS up from nodes left join nodeassignments on nodes.id = nodeassignments.nodeid left join assignments on nodeassignments.assignmentid = assignments.id left join tariffs on assignments.tariffid = tariffs.id ;}\ {for (r=0; r<number(result); r++)}{if (result[r].passwd != "")}\ "{result[r].name}"{"\tCleartext-Password := "}"{result[r].passwd}" {"\t\tFramed-IP-Address = "}{result[r].ip}, {"\t\tMikrotik-Rate-Limit = "}{result[r].down"K"}/{result[r].up"K"} {/if}{/for}\
Wiec mam takie cos:
select nodes.name,nodes.ipaddr,nodes.passwd,tariffs.upceil,tariffs.downceil from nodes left join nodeassignments on nodes.id = nodeassignments.nodeid left join assignments on nodeassignments.assignmentid = assignments.id left join tariffs on assignments.tariffid = tariffs.id left join customers on assignments.customerid = customers.id where customers.status = 3 and (assignments.datefrom <= UNIX_TIMESTAMP( ) OR assignments.datefrom = 0) AND (assignments.dateto > UNIX_TIMESTAMP( ) OR assignments.dateto = 0) and nodes.passwd != '' ;
Jedyny problem jaki zostal mi do rozwiazania to kwestia sytuacji, kiedy np host1 ma przypisane dwa aktywne zobowiazania, np: downceil 2 mbit i drugi downceil 5 mbit. Wowczas zostaje on zduplikowany w wyniku, a powinien wystapic jeden raz oraz miec zsumowane upceil i downceil ze zobowiazan ktore ma przypisane.
Jak powinno wygladac zapytanie: - powtarzajacy sie host wyswietl jeden raz, i zsumuj powtarzajace sie rekordy dla tego hosta z kolumny upceil i downceil.
Za pomoc dziekuje z gory. Pozdrawiam.