CODEWITHSUNDEEP
mysql
Blue Eden
Blue Eden

Reputation: 59

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.

tableB holds information relating to the hirage.

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?

Upvotes: 1

Views: 155

Answers (2)

ysth
ysth

Reputation: 98388

Or just use a join

select a.* from TableA a
left join TableB b
    on b.barcode = a.barcode
      and now() between hiredOn and dueBack
where b.barcode is null;

Upvotes: 0

jpw
jpw

Reputation: 44881

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.

Upvotes: 1

Related Questions