Databases
StackOverflow2013
Postsdbo
POHow to query for an available item
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to query for an available item
    primarykey
    data
    text
    <p>I just would like to ask for help concerning this equipment inventory and reservation system i am working on...</p> <p>So... i have 3 tables. <code>tbl_items</code>, <code>tbl_bulk_items</code>, and <code>tbl_reservations</code>.</p> <p><strong>tbl_items</strong> contains all the items(equipment classified as per UNIT)</p> <p><strong>tbl_items_bulk</strong> contains all the items that are in bulk. e.g. a set of cooking knives(12 pcs/set)</p> <p><strong>tbl_reservations</strong> contains all the reservation information</p> <p>so far, this is my query for getting all the items in both the <code>tbl_items</code> and <code>tbl_items_bulk</code> tables.</p> <pre><code>SELECT bcode FROM /*gets all the items in the inventory*/ (SELECT bcode FROM tbl_items AS T1 UNION SELECT bcode FROM tbl_items_bulk) AS T2 </code></pre> <p>And i would query it against another query to get a list of all the bcodes that doesn't exist in the tbl_reservation(meaning it's available)...</p> <pre><code>/*gets all the items in the inventory that satisfies the given conditions*/ WHERE bcode NOT IN (SELECT bcode FROM tbl_test) </code></pre> <p>But the problem is, i need to use conditions(dont quite know how to properly do that) to further filter the query to get all the 'available' items.</p> <p>here's the conditions...</p> <p>if the item's bcode is in the reservation table, then it IS unavailable. OR if it(item bcode) is in the reservation table, but the date and time of the reservation is different from what the user will state, then it can still be counted as available.</p> <p>e.g. Equipment 1 was reserved 2013-09-16 from 7:30 AM to 10:30 AM. If the user would ask if it is available for another date(let's say 2013-09-17), from 7:30 AM to 10:30 PM, it should show up as 'available'. (I hope I'm making it clearer)</p> <p>Any ideas as to how I would be able to get all 'available' equipment for a certain date and time?</p> <p>My current code:</p> <pre><code>SELECT bcode FROM /*gets all the items in the inventory*/ (SELECT bcode FROM tbl_items AS T1 UNION SELECT bcode FROM tbl_items_bulk) AS T2 /*gets all the items in the inventory that satisfies the given conditions*/ WHERE bcode NOT IN (SELECT bcode FROM tbl_test WHERE resDate!='2013-09-16') </code></pre> <p>UPDATE 9-17-2013:</p> <p>LATEST CODE:</p> <pre><code>SELECT b.* FROM (SELECT * FROM tbl_items UNION SELECT * FROM tbl_items_bulk ) b left outer join tbl_test t on b.bcode = t.bcode and NOT ('4:30' &lt; t.timeSTART OR '7:00' &gt; t.timeEND) WHERE t.bcode is null; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser2784251
    1. USuser2784251
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to query for an available item
      1. USwesleywmd
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload