Re: [lms] Postresql - zapytanie sql (raport)

W dniu 11-11-10 12:50, Paweł Mendela pisze:

Witam,

Napisałem sobie dwa zapytania na potrzeby raportu który chcę zrobić, nie wiem tylko jak na poziomie sql połączyć te zapytania aby wynik pojawiał się w momencie gdy customer.id jest wynikiem RÓWNOCZEŚNIE obydwu zapytań.

Sposób mało wydajny i brzydki ale skuteczny:

- podzapytania:

SELECT customers.id, customers.lastname, customers.name, customers.address, customers.city, customers.zip, tariffs.value, tariffs.period, assignments.dateto, assignments.at FROM customers LEFT JOIN assignments ON customers.id = customerid LEFT JOIN tariffs ON assignments.tariffid = tariffs.id WHERE customers.id IN (SELECT customers.id FROM customers LEFT JOIN assignments ON customers.id = customerid LEFT JOIN tariffs ON assignments.tariffid = tariffs.id WHERE deleted = 0 AND (customers.status = 3) AND (assignments.invoice = 1) AND (assignments.dateto >= (select now()::abstime::int4) OR assignments.dateto = 0) AND (assignments.at != 1) AND (tariffs.value BETWEEN 1 AND 4) GROUP BY customers.id) AND customers.id IN (SELECT customers.id FROM customers LEFT JOIN assignments ON customers.id = customerid LEFT JOIN tariffs ON assignments.tariffid = tariffs.id WHERE deleted = 0 AND (customers.status = 3) AND (assignments.invoice = 1) AND (assignments.dateto >= (select now()::abstime::int4) OR assignments.dateto = 0) AND (assignments.at = 1) AND (tariffs.value >= 4) GROUP BY customers.id);

- pokręcone warunki:

SELECT customers.id, customers.lastname, customers.name, customers.address, customers.city, customers.zip, tariffs.value, tariffs.period, assignments.dateto, assignments.at FROM customers LEFT JOIN assignments ON customers.id = customerid LEFT JOIN tariffs ON assignments.tariffid = tariffs.id WHERE ( deleted = 0 AND (customers.status = 3) AND (assignments.invoice = 1) AND (assignments.dateto >= (select now()::abstime::int4) OR assignments.dateto = 0) ) AND ( ((assignments.at != 1) AND (tariffs.value BETWEEN 1 AND 4)) AND ((assignments.at = 1) AND (tariffs.value >= 4)) ) GROUP BY customers.id, customers.lastname, customers.name, customers.address, customers.city, customers.zip, tariffs.value, tariffs.period, assignments.dateto, assignments.at;

Zrobione na szybcika "na kolanie", nie ma pod ręką odpowiedniej bazy aby to debugować, możliwe sa tez błędy składni i nawiasów.

Będę wdzięczny za pomoc.

piwo starczy, jak zadziała ;)