Blue Eden Blue Eden - 2 years ago 57
SQL Question

MySQL Get values without Foreign Key Constraints AND Compare Dates Different Tables

Say I own a costume shop or something a long those lines.

tableA holds the information for the costume.

  • barcode (fk REFERENCES tableB)

  • type (i.e. Hulk, Superman etc.)

  • hirageFee

tableB holds information relating to the hirage.

  • barcode (REFERENCES tableA)

  • hiredOn (date)

  • dueBack (date)

  • transaction no.

I am trying to write a query that will check if the costume is available to rent.

I can use the following to find all costumes that haven't been rented ever:

SELECT * FROM tableA WHERE barcode NOT IN (SELECT barcode FROM tableB)

and to check current rentals that are back in the store already:

SELECT * FROM tableB WHERE dueBack < ('enter-current-date');

But I am stuck on how to combine these queries or if that's the right approach to see/find all costumes currently in store?

jpw jpw
Answer Source

If you want to see what items in TableA that are not rented out you can restate the question as which items doesn't have a rented out period that the current date falls in

Expressed in SQL it would be a a query using a negated exists predicate with a correlated subquery, like this:

select * from TableA a
where not exists (
    select *
    from TableB b 
    where b.barcode = a.barcode
      and now() between hiredOn and dueBack

There are many other ways to achieve the same result, but I think this fits the semantics of the question well.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download