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

[Michael Hafen]

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 at 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 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
>
>
> On Wed, Jun 26, 2019 at 10:44 PM Michael Hafen <michael.hafen at 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 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/20190627/a06ef173/attachment.html>