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 | University Email

LinkedIn |  GitHub 

▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄

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/