Need SQL query help, please
I have a query to find borrowers with late books that do not have email addresses or mobiles. Now I am trying to modify it so it will show their school from attributes if they have one but won't knock them off the list if they don't. My modification below shows only those with no email and no mobile and a school but knocks people off the list who have no school attribute. How can I modify it so it includes no mobile no email and lists school for those who have one but still includes those who don't? SELECT borrowers.surname,borrowers.firstname,borrowers.mobile,borrowers.email,borrowers.city, biblio.title,biblioitems.itemtype,issues.date_due,borrower_attributes.attribute FROM borrowers LEFT JOIN borrower_attributes on (borrower_attributes.borrowernumber = borrowers.borrowernumber) LEFT JOIN issues on (issues.borrowernumber = borrowers.borrowernumber) LEFT JOIN items on (items.itemnumber = issues.itemnumber) LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) WHERE issues.date_due < DATE_SUB(now(), INTERVAL 8 DAY) AND issues.returndate IS NULL AND borrowers.mobile = ' ' AND borrowers.email = ' ' AND borrower_attributes.code = 'School' ORDER BY borrowers.surname asc; Thanks for your help! Carol -- supporting Lao youth in their quest for a brighter future.... The Language Project…an American 501(c)3 charity Website: www.thelanguageproject.org Gallery: http://laophotographers.zenfolio.com Lao address: PO Box 905, Luang Prabang, Laos Lao mobile: 856 20 97122771 US address: PO Box 13201, Tallahassee, FL 32317
On 4 April 2012 00:32, Carol Kresge <carol.kresge@gmail.com> wrote:
I have a query to find borrowers with late books that do not have email addresses or mobiles. Now I am trying to modify it so it will show their school from attributes if they have one but won't knock them off the list if they don't. My modification below shows only those with no email and no mobile and a school but knocks people off the list who have no school attribute. How can I modify it so it includes no mobile no email and lists school for those who have one but still includes those who don't?
SELECT borrowers.surname,borrowers.firstname,borrowers.mobile,borrowers.email,borrowers.city, biblio.title,biblioitems.itemtype,issues.date_due,borrower_attributes.attribute FROM borrowers LEFT JOIN borrower_attributes on (borrower_attributes.borrowernumber = borrowers.borrowernumber) LEFT JOIN issues on (issues.borrowernumber = borrowers.borrowernumber) LEFT JOIN items on (items.itemnumber = issues.itemnumber) LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) WHERE issues.date_due < DATE_SUB(now(), INTERVAL 8 DAY) AND issues.returndate IS NULL AND borrowers.mobile = ' ' AND borrowers.email = ' ' AND borrower_attributes.code = 'School' ORDER BY borrowers.surname asc;
Hi Carol I think this will do it SELECT surname, firstname, mobile, email, city, biblio.title, biblioitems.itemtype, issues.date_due, (select borrower_attributes.attribute from borrower_attributes where code = 'School') as School FROM borrowers LEFT OUTER JOIN borrower_attributes on (borrower_attributes.borrowernumber = borrowers.borrowernumber) LEFT JOIN issues on (issues.borrowernumber = borrowers.borrowernumber) LEFT JOIN items on (items.itemnumber = issues.itemnumber) LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) WHERE issues.date_due < DATE_SUB(now(), INTERVAL 8 DAY) AND issues.returndate IS NULL AND borrowers.mobile = ' ' AND borrowers.email = ' ' ORDER BY borrowers.surname asc; Chris
Ohh, This will actually work better SELECT surname, firstname, mobile, email, city, biblio.title, biblioitems.itemtype, issues.date_due, (select borrower_attributes.attribute from borrower_attributes where code = 'School' group by borrowernumber) as School FROM borrowers LEFT OUTER JOIN borrower_attributes on (borrower_attributes.borrowernumber = borrowers.borrowernumber) LEFT JOIN issues on (issues.borrowernumber = borrowers.borrowernumber) LEFT JOIN items on (items.itemnumber = issues.itemnumber) LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) WHERE issues.date_due < DATE_SUB(now(), INTERVAL 8 DAY) AND issues.returndate IS NULL AND borrowers.mobile = ' ' AND borrowers.email = ' ' GROUP BY borrowers.borrowernumber,items.itemnumber ORDER BY borrowers.surname asc; And avoid double ups Chris
Thanks, but I get an error with both and it won't run error: 1242 subquery returns more than 1 row Maybe that is because I should have mentioned that the User attribute "school" goes to an authorised value category and has a lot of different values (they go to many different schools...Phongkham, Pasatipatai, Phasabandith...) Sorry, do you have another idea? Thanks, Carol On Wed, Apr 4, 2012 at 4:32 AM, Chris Cormack <chris@bigballofwax.co.nz>wrote:
Ohh,
This will actually work better
SELECT surname, firstname, mobile, email, city, biblio.title, biblioitems.itemtype, issues.date_due, (select borrower_attributes.attribute from borrower_attributes where code = 'School' group by borrowernumber) as School FROM borrowers LEFT OUTER JOIN borrower_attributes on (borrower_attributes.borrowernumber = borrowers.borrowernumber) LEFT JOIN issues on (issues.borrowernumber = borrowers.borrowernumber) LEFT JOIN items on (items.itemnumber = issues.itemnumber) LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) WHERE issues.date_due < DATE_SUB(now(), INTERVAL 8 DAY) AND issues.returndate IS NULL AND borrowers.mobile = ' ' AND borrowers.email = ' ' GROUP BY borrowers.borrowernumber,items.itemnumber ORDER BY borrowers.surname asc;
And avoid double ups
Chris
-- supporting Lao youth in their quest for a brighter future.... The Language Project…an American 501(c)3 charity Website: www.thelanguageproject.org Gallery: http://laophotographers.zenfolio.com Lao address: PO Box 905, Luang Prabang, Laos Lao mobile: 856 20 97122771 US address: PO Box 13201, Tallahassee, FL 32317
On 4 April 2012 14:53, Carol Kresge <carol.kresge@gmail.com> wrote:
Thanks, but I get an error with both and it won't run error: 1242 subquery returns more than 1 row
Maybe that is because I should have mentioned that the User attribute "school" goes to an authorised value category and has a lot of different values (they go to many different schools...Phongkham, Pasatipatai, Phasabandith...) Sorry, do you have another idea?
Ah my bad, The do only go to one school each though right? Or can one student be at more than one? Chris
They only go to one school each. another possibility is they do have more than 1 item checked out On Wed, Apr 4, 2012 at 9:58 AM, Chris Cormack <chris@bigballofwax.co.nz>wrote:
On 4 April 2012 14:53, Carol Kresge <carol.kresge@gmail.com> wrote:
Thanks, but I get an error with both and it won't run error: 1242 subquery returns more than 1 row
Maybe that is because I should have mentioned that the User attribute "school" goes to an authorised value category and has a lot of different values (they go to many different schools...Phongkham, Pasatipatai, Phasabandith...) Sorry, do you have another idea?
Ah my bad,
The do only go to one school each though right? Or can one student be at more than one?
Chris
-- supporting Lao youth in their quest for a brighter future.... The Language Project…an American 501(c)3 charity Website: www.thelanguageproject.org Gallery: http://laophotographers.zenfolio.com Lao address: PO Box 905, Luang Prabang, Laos Lao mobile: 856 20 97122771 US address: PO Box 13201, Tallahassee, FL 32317
On 4 April 2012 15:12, Carol Kresge <carol.kresge@gmail.com> wrote:
They only go to one school each. another possibility is they do have more than 1 item checked out
Writing this without the data to test, but I *think* this will do it. SELECT surname, firstname, mobile, email, city, biblio.title, biblioitems.itemtype, issues.date_due, (select borrower_attributes.attribute from borrower_attributes where code = 'School' and borrower_attributes.borrowernumber=borrowers.borrowernumber) as School FROM borrowers LEFT OUTER JOIN borrower_attributes on (borrower_attributes.borrowernumber = borrowers.borrowernumber) LEFT JOIN issues on (issues.borrowernumber = borrowers.borrowernumber) LEFT JOIN items on (items.itemnumber = issues.itemnumber) LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) WHERE issues.date_due < DATE_SUB(now(), INTERVAL 8 DAY) AND issues.returndate IS NULL AND borrowers.mobile = ' ' AND borrowers.email = ' ' GROUP BY borrowers.borrowernumber,items.itemnumber ORDER BY borrowers.surname asc; Fingers crossed Chris
Yes! perfect...thanks. On Wed, Apr 4, 2012 at 10:14 AM, Chris Cormack <chris@bigballofwax.co.nz>wrote:
On 4 April 2012 15:12, Carol Kresge <carol.kresge@gmail.com> wrote:
They only go to one school each. another possibility is they do have more than 1 item checked out
Writing this without the data to test, but I *think* this will do it.
SELECT surname, firstname, mobile, email, city, biblio.title, biblioitems.itemtype, issues.date_due, (select borrower_attributes.attribute from borrower_attributes where code = 'School' and borrower_attributes.borrowernumber=borrowers.borrowernumber) as School FROM borrowers LEFT OUTER JOIN borrower_attributes on (borrower_attributes.borrowernumber = borrowers.borrowernumber) LEFT JOIN issues on (issues.borrowernumber = borrowers.borrowernumber) LEFT JOIN items on (items.itemnumber = issues.itemnumber) LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) WHERE issues.date_due < DATE_SUB(now(), INTERVAL 8 DAY) AND issues.returndate IS NULL AND borrowers.mobile = ' ' AND borrowers.email = ' ' GROUP BY borrowers.borrowernumber,items.itemnumber ORDER BY borrowers.surname asc;
Fingers crossed
Chris
-- supporting Lao youth in their quest for a brighter future.... The Language Project…an American 501(c)3 charity Website: www.thelanguageproject.org Gallery: http://laophotographers.zenfolio.com Lao address: PO Box 905, Luang Prabang, Laos Lao mobile: 856 20 97122771 US address: PO Box 13201, Tallahassee, FL 32317
participants (2)
-
Carol Kresge -
Chris Cormack