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.
-- Matys Łukasz
Temat rozwiazany, dla potomnych:
{result = SELECT LOWER(nodes.name) AS name, inet_ntoa(nodes.ipaddr) AS ip , nodes.passwd AS passwd, SUM(tariffs.downceil) AS downceil, SUM(tariffs.upceil) AS upceil 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 != '' group by nodes.name;}\ {for (r=0; r<number(result); r++)}{if (result[r].passwd != "")}\ "{result[r].name}"{"\t\tCleartext-Password := "}"{result[r].passwd}" {"\t\t\tFramed-IP-Address = "}{result[r].ip}, {"\t\t\tMikrotik-Rate-Limit = "}{result[r].downceil"k"}/{result[r].upceil"k"} {/if}{/for}\
Pozdrawiam.