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: koha-boun...@lists.katipo.co.nz [mailto:koha-boun...@lists.katipo.co.nz] 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 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 _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha