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
bgkrie...@gmail.com
On Mon, Apr 22, 2013 at 4:18 PM, Nicole Engard <neng...@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@lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>
_______________________________________________
Koha mailing list http://koha-community.org
Koha@lists.katipo.co.nz
http://lists.katipo.co.nz/mailman/listinfo/koha
