[Koha-devel] SQL Query to get the Waiting Holds From reserves table.

[Michael Hafen]

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 at 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 at yahoo.in> | University Email
> <f20171016 at 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 at 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.koha-community.org/pipermail/koha-devel/attachments/20190626/d9cfb602/attachment.html>