It's a left outer join, which means we will get all the applicable rows from reserves but not necessarily from issues. If there isn't a matching row in issues then we get null values instead. The date_due column should never be null otherwise.
So that query should show reserves with no issue. On Wed, Jun 26, 2019, 10:50 PM RAGHAV ARORA < f20171...@pilani.bits-pilani.ac.in> wrote: > Thank you for your reply Sir. > > Why are we querying for issues that have Null date_due? Should we not > query for the reserves corresponding to which no issues exist. > > When will the date_due be null? > > Regards, > *Raghav Arora* > Sophomore, M.Sc (Hons) Chemistry BE Electrical and Electronics Engineering > Contact : (+91) 9897597761 > Personal Email <raghavarora...@yahoo.in> | University Email > <f20171...@pilani.bits-pilani.ac.in> > LinkedIn <https://www.linkedin.com/in/raghav-arora-9820a648/> | GitHub > <https://github.com/RAraghavarora/> > > ▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄ > *Birla Institute of Technology and Science, Pilani* > Pilani campus, > Rajasthan-333031 > > > On Wed, Jun 26, 2019 at 10:44 PM Michael Hafen <michael.ha...@washk12.org> > wrote: > >> Here's me trying the query with more joins instead of sub-selects, though >> I can't guarantee it's exactly what you want. >> >> SELECT borrowers.surname, borrowers.email, borrowers.cardnumber, >> it1.barcode, biblio.title, reserves.reservedate >> FROM reserves >> LEFT JOIN borrowers USING (borrowernumber) >> LEFT JOIN biblio USING (biblionumber) >> LEFT JOIN items AS it1 USING (itemnumber) >> LEFT JOIN issues AS iss1 USING (itemnumber) >> LEFT JOIN items AS it2 ON (reserves.biblionumber = it2.biblionumber) >> LEFT JOIN issues AS iss2 ON(it2.itemnumber = iss2.itemnumber) >> WHERE iss1.date_due IS NULL AND iss2.date_due IS NULL >> >> >> On Wed, Jun 26, 2019 at 12:45 AM RAGHAV ARORA < >> f20171...@pilani.bits-pilani.ac.in> wrote: >> >>> Hello all, >>> >>> Could someone please help me in creating the SQL query to get all the >>> holds in the library for which the book is available,in the library (i.e, >>> no issue exists for that book) >>> >>> The reserves table contains some rows for which itemnumber is Null, and >>> the waitingdate is Null for every row even if no issue exists for the item. >>> >>> I came up with the following sql query, but I am not sure if I am right, >>> can someone please verify my query, or rectify it with the correct query: >>> >>> ```SELECT >>> borrowers.surname, borrowers.email, borrowers.cardnumber, >>> items.barcode, biblio.title, reserves.reservedate >>> FROM reserves >>> LEFT JOIN borrowers USING (borrowernumber) >>> LEFT JOIN items USING (itemnumber) >>> LEFT JOIN biblio ON (reserves.biblionumber = biblio.biblionumber) >>> WHERE >>> (reserves.itemnumber IS NOT NULL >>> AND NOT EXISTS(SELECT issue_id FROM issues WHERE >>> items.itemnumber = issues.itemnumber) >>> ) >>> OR >>> (reserves.itemnumber IS NULL >>> AND EXISTS( >>> SELECT itemnumber FROM items i2 WHERE >>> items.biblionumber=reserves.biblionumber >>> AND NOT EXISTS( >>> SELECT issue_id FROM issues WHERE >>> issues.itemnumber=i2.itemnumber >>> ) >>> ) >>> ) >>> ``` >>> >>> Thanks in advance >>> Regards >>> *Raghav Arora* >>> Sophomore, M.Sc (Hons) Chemistry BE Electrical and Electronics >>> Engineering >>> Contact : (+91) 9897597761 >>> Personal Email <raghavarora...@yahoo.in> | University Email >>> <f20171...@pilani.bits-pilani.ac.in> >>> LinkedIn <https://www.linkedin.com/in/raghav-arora-9820a648/> | GitHub >>> <https://github.com/RAraghavarora/> >>> >>> ▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄ >>> *Birla Institute of Technology and Science, Pilani* >>> Pilani campus, >>> Rajasthan-333031 >>> _______________________________________________ >>> Koha-devel mailing list >>> Koha-devel@lists.koha-community.org >>> http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel >>> website : http://www.koha-community.org/ >>> git : http://git.koha-community.org/ >>> bugs : http://bugs.koha-community.org/ >> >> >> >> -- >> Michael Hafen >> Washington County School District Technology Department >> Systems Analyst >> >>
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
