Thank you so much - so simple!! :)
On Wed, Apr 10, 2013 at 2:45 PM, Bernardo Gonzalez Kriegel < [email protected]> wrote: > 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
