MySQL Forums
Forum List  »  Stored Procedures

How to join two tables through Select and Call Procedure.
Posted by: Roger Novelo
Date: April 09, 2019 01:05PM

Hello,

I have two tables INVENTORY and SCHEDULE. I want to be able to and query both tables through a SELECT STATEMENT but at the same time use a MySQL Procedure to Call isavailable and check if the date range is avaiable. Eg:

INVENTORY -table looks similar to this.(shortened the fields)

ID Make | Model | Lic_No | Rate | Active |
17 | Jeep | Liberty | C-34104 | 90.00 | Y |
18 | Jeep | Liberty | C-36983 | 90.00 | Y |
27 | Suzuki | Grand Vitara | C-42023 | 85.00 | Y |
34 | Toyota | Hilux | C-44638 | 95.00 | Y |
35 | Nissan | Pathfinder | C-52840 | 105.00 | Y |
36 | Jeep | Liberty | C-28172 | 75.00 | Y |
37 | Jeep | Liberty | C-33046 | 80.00 | Y |
39 | Jeep | Liberty | C-36984 | 85.00 | Y |
40 | Dodge | Nitro | C-40786 | 85.00 | Y |
41 | Jeep | Liberty | C-43601 | 90.00 | Y |

SCHEDULE
ID | vehicleID | startDate | endDate |
1 | 40 | 2019-04-05 | 2019-04-16 |
2 | 40 | 2019-04-19 | 2019-04-22 |

I need join two statements

Query1 = "SELECT * FROM `inventory` ORDER BY ID ASC";

and

Query2 = "CALL isavailable( 'ID','sDate','eDate')";

This is my Procedure.
DROP PROCEDURE IF EXISTS isavailable;
DELIMITER |
CREATE PROCEDURE isavailable( ID int, sDate date, eDate date )
SELECT IF( COUNT(1),'No','Yes' ) AS Available
FROM schedule
WHERE vehicleID = ID
AND startDate < eDate
AND endDate > sDate;
|
DELIMITER ;



I would like to out put to be something like this:


vehicleID | Avaialble|

... | Yes
38 | Yes
39 | Yes
40 | No
41 | Yes


I would appreciate if anyone can help me join these two queries. This is the first time I'm working with a procedure.

Options: ReplyQuote


Subject
Views
Written By
Posted
How to join two tables through Select and Call Procedure.
1188
April 09, 2019 01:05PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.