[Koha] Report Help

[Bernardo Gonzalez Kriegel]

Nicole,
you want for each guarantor it's guarantees, Q: it's ok to repeat guarantor
information on each guarantee tuple?

you could use something like this (put any data you want in first select,
and change order by if needed)

SELECT
  IFNULL(concat(g.surname, ', ', g.firstname, ' (',g.cardnumber, ')'),'')
AS guarantor,
  IFNULL(concat(p.surname, ', ', p.firstname, ' (',p.cardnumber, ')'),'')
AS guarantee
FROM
(
  SELECT *
  FROM borrowers
  WHERE guarantorid  IS NOT NULL
) AS p
LEFT JOIN borrowers AS g
  ON p.guarantorid  = g.borrowernumber
ORDER BY g.borrowernumber
;

Bernardo

-- 
Bernardo Gonzalez Kriegel
bgkriegel at gmail.com


On Mon, Apr 22, 2013 at 4:18 PM, Nicole Engard <nengard at gmail.com> wrote:

> I'm looking to change this report around.  It shows patrons with their
> guarantor information.  I want a report that shows me the guarantees for
> each guarantor instead.
>
>
> SELECT p.categorycode, p.dateofbirth, p.cardnumber, p.surname, p.firstname,
> p.dateexpiry,  IFNULL(concat(g.surname, ', ', g.firstname, ' (',
> g.cardnumber, ')'),'') AS guarantor, p.relationship,
> ifnull(FORMAT(SUM(a.amountoutstanding),2),'0.00') AS due
> FROM borrowers p
> LEFT JOIN accountlines a USING (borrowernumber)
> LEFT JOIN borrowers g ON (p.guarantorid=g.borrowernumber)
> WHERE p.dateexpiry < NOW()
> GROUP BY p.borrowernumber
> ORDER BY p.dateexpiry ASC
>
>
>
> Nicole
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>