Re: [Koha-devel] SQL Query to get the Waiting Holds From reserves table.
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 < f20171016@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 <raghavarora999@yahoo.in> | University Email <f20171016@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.hafen@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 < f20171016@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 <raghavarora999@yahoo.in> | University Email <f20171016@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
Oh! I understand now. Thank you so much, I think this should work. Regards *Raghav Arora* Sophomore, M.Sc (Hons) Chemistry BE Electrical and Electronics Engineering Contact : (+91) 9897597761 Personal Email <raghavarora999@yahoo.in> | University Email <f20171016@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 Thu, Jun 27, 2019 at 9:00 PM Michael Hafen <michael.hafen@washk12.org> wrote:
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 < f20171016@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 <raghavarora999@yahoo.in> | University Email <f20171016@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.hafen@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 < f20171016@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 <raghavarora999@yahoo.in> | University Email <f20171016@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
participants (2)
-
Michael Hafen -
RAGHAV ARORA