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
bgkrie...@gmail.com
On Wed, Apr 10, 2013 at 2:28 PM, Nicole Engard <neng...@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 <neng...@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@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
