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

[RAGHAV ARORA]

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 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 Thu, Jun 27, 2019 at 9:00 PM Michael Hafen <michael.hafen at 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 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/20190628/09cdcc0f/attachment-0001.html>