[Koha] Report Help

[Bernardo Gonzalez Kriegel]

Nicole,
what you need to match is p(atron).guarantorid with
g(uarantor).borrowernumber,
not g(uarantor).guarantorid with p(atron).borrowernumber
that is what causing you to get an inverted list.

For example this query will list all guarantees with corresponding
guarantor (if not null),

SELECT
    patron.surname, patron.firstname, patron.relationship,
patron.guarantorid,
    guarantor.borrowernumber, guarantor.surname, guarantor.firstname
FROM
    borrowers as patron
LEFT JOIN
    borrowers as guarantor
ON ( patron.guarantorid = guarantor.borrowernumber )
WHERE patron.guarantorid IS NOT NULL;


Regards,
Bernardo

-- 
Bernardo Gonzalez Kriegel
bgkriegel at gmail.com


On Wed, Apr 10, 2013 at 2:28 PM, Nicole Engard <nengard at gmail.com> wrote:

> Sorry - I was wrong - not patrons with overdues, but expired patrons -
> that's what I'm looking for.
>
>
> On Wed, Apr 10, 2013 at 1:25 PM, Nicole Engard <nengard at gmail.com> wrote:
>
> > I'm trying to get a report to show patrons with overdues and if there is
> a
> > guarantor I want that to show too. But what's happening is the Guarantees
> > are showing as the Guarantors and the Guarantor is showing 2 times cause
> he
> > has 2 Guarantees - can someone help me with my SQL:
> >
> >
> > 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,
> > FORMAT(SUM(a.amountoutstanding),2) as due
> > FROM borrowers p
> > LEFT JOIN accountlines a USING (borrowernumber)
> > left join borrowers g on (g.guarantorid=p.borrowernumber)
> > WHERE p.dateexpiry < NOW() and p.surname = 'Meave'
> > group by p.borrowernumber, g.borrowernumber
> > ORDER BY p.dateexpiry asc ;
> >
> >
> >
> >
> >
> >
> +--------------+-------------+------------+---------+---------------+------------+---------------------------------+--------------+--------+
> > | categorycode | dateofbirth | cardnumber | surname | firstname     |
> > dateexpiry | guarantor                       | relationship | due    |
> >
> >
> +--------------+-------------+------------+---------+---------------+------------+---------------------------------+--------------+--------+
> > | TRES         | 1996-08-24  | P0012129   | Meave   | Melina-Maria  |
> > 2008-12-04 |                                 | parent       | 5.90   |
> > | TRES         | 2000-08-30  | P0012128   | Meave   | Phoenix-Jonas |
> > 2008-12-04 |                                 | parent       | 2.30   |
> > | STRICT       | 1956-09-23  | P0012127   | Meave   | Jose          |
> > 2008-12-04 | Meave, Melina-Maria (P0012129)  |              | 351.53 |
> > | STRICT       | 1956-09-23  | P0012127   | Meave   | Jose          |
> > 2008-12-04 | Meave, Phoenix-Jonas (P0012128) |              | 351.53 |
> >
> >
> +--------------+-------------+------------+---------+---------------+------------+---------------------------------+--------------+--------+
> >
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>