As the original request was "I'm trying to get a report to show patrons with
overdues and if there is a guarantor I want that to show too", might I
propose the following modification to Bernardo Gonzalez Kriegel's reply, so
that all patrons are shown in a single report:
select * from
(
SELECT
patron.borrowernumber as borrower,
patron.surname as Surname,
patron.firstname as Firstname,
patron.relationship as relationship,
guarantor.borrowernumber as guarantor,
guarantor.surname as guarantor_Surname,
guarantor.firstname as guarantor_Firstname
FROM
borrowers as patron
LEFT JOIN
borrowers as guarantor
ON ( patron.guarantorid = guarantor.borrowernumber ) WHERE
patron.guarantorid IS NOT NULL
UNION
SELECT
patron.borrowernumber as borrower,
patron.surname as Surname,
patron.firstname as Firstname,
patron.relationship as relationship,
'-' as guarantor,
'-' as guarantor_Surname,
'-' as guarantor_Firstname
FROM
borrowers as patron
WHERE patron.guarantorid IS NULL
) tmp
order by tmp.Surname, tmp.Firstname;
Kind regards,
Manos Petridis
-----Original Message-----
From: [email protected]
[mailto:[email protected]] On Behalf Of Bernardo Gonzalez
Kriegel
Sent: Wednesday, April 10, 2013 9:45 PM
To: Nicole Engard
Cc: Koha
Subject: Re: [Koha] Report Help
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
[email protected]
On Wed, Apr 10, 2013 at 2:28 PM, Nicole Engard <[email protected]> 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 <[email protected]> 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 [email protected]
> http://lists.katipo.co.nz/mailman/listinfo/koha
>
_______________________________________________
Koha mailing list http://koha-community.org [email protected]
http://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________
Koha mailing list http://koha-community.org
[email protected]
http://lists.katipo.co.nz/mailman/listinfo/koha
