CODEWITHSUNDEEP
mysqlsqlselect
user2784251
user2784251

Reputation: 41

How to query for an available item

I just would like to ask for help concerning this equipment inventory and reservation system i am working on...

So... i have 3 tables. tbl_items, tbl_bulk_items, and tbl_reservations.

tbl_items contains all the items(equipment classified as per UNIT)

tbl_items_bulk contains all the items that are in bulk. e.g. a set of cooking knives(12 pcs/set)

tbl_reservations contains all the reservation information

so far, this is my query for getting all the items in both the tbl_items and tbl_items_bulk tables.

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

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)...

/*gets all the items in the inventory that satisfies the given conditions*/
WHERE bcode
NOT IN
(SELECT bcode FROM tbl_test)

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.

here's the conditions...

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.

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)

Any ideas as to how I would be able to get all 'available' equipment for a certain date and time?

My current 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')

UPDATE 9-17-2013:

LATEST 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' < t.timeSTART OR '7:00' > t.timeEND)
WHERE t.bcode is null;

Upvotes: 0

Views: 198

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269893

It sounds like you are looking to see if there are any reservations for bcode at a certain time. I think the following query has the right logic:

SELECT b.bcode
FROM (SELECT bcode FROM tbl_items
      UNION
      SELECT bcode FROM tbl_items_bulk
     ) b left outer join
     tbl_reservation r
     on b.bcode = r.bcode and
        @USERTO <= r.ToDate and
        @USERFROM >= r.FromDate
WHERE r.bcode is null;

What this does is check to see if there are any reservations that match the bcode for a given date using left outer join. Note the date condition is in the on clause. So, if there is a match, then a row will be returned. If there is no match, then the bcode value from the reservations table will be NULL -- which is what the where clause filters for.

Upvotes: 1

Andrew
Andrew

Reputation: 8703

I can't add a comment, but to answer your question about what the r.bcode is null is doing.

The left outer join means give me all rows from the left table (in this case the result of the union), and all matching rows from the right side (table_reservation). If a row in the union resultset doesn't have a match in table_reservation, bcode will return as null. So filtering for nulls on that column will return all rows that do not have a match in table_reservation.

Upvotes: 0

Related Questions