CODEWITHSUNDEEP
phpmysql
Shibbir
Shibbir

Reputation: 409

How can I get data from 2 table based on specific condition

In MySQL database, I have 3 table called supplier, vehicle, and assigned table.

vehicle table:

vid     vehicleName     noOfSeat     sid
========================================
13      x               10           28 
14      x               8            28
15      x               12           28
16      x               14           29 
17      x               4            29
18      x               8            30

assigned table

asgid   bid  sid  vid   did  seatBooked   assigned_start    assigned_end
============================================================================
56      15   28   13    17   3            06/01/2018 02:01  06/01/2018 04:02
57      15   28   14    15   2            06/01/2018 02:01  06/01/2018 04:02
58      15   28   15    16   3            06/01/2018 02:01  06/01/2018 04:02

In a reservation page, I need to assign a vehicle to a selected date which is 06/01/2018 02:01 To 06/01/2018 04:02

Now I want to show all vehicle of a selected supplier as well as the number of seat from the vehicle table

But the condition is:

Those vehicles will be shown which has the number of seats available for my selected date: 6/01/2018 02:01 To 06/01/2018 04:02

For example:

It's must be shown following vehicle with the number of seats:

vid    Mp pf Seat available
===========================
13     7 ( why 7? because on my selected date 3 seats booked)
14     6 ( why 6? because on my selected date 2 seats booked)
15     9 ( why 9? because on my selected date 3 seats booked)
16     14
17     4
18     8

Its something like bus ticket system.

My Current Query:

Currently, I am using the following query but it just removes those vid which is in my selected date but it's incorrect because as you can see that on my selected date few seats are available!!! 

// my selected date: From = '$timestart' and To = '$timeend'
foreach ($sid as $key => $value) {
    $vehicle->rowQuery("select * from vehicle where vid not in (
    select vid from assigned where assigned_start BETWEEN '$timestart' AND '$timeend' AND assigned_end BETWEEN '$timestart' AND '$timeend' ) AND sid = $value ");
}

I can't imagine how can I solve it: =(

Your help is highly appreciated.

Upvotes: 2

Views: 68

Answers (1)

HTMHell
HTMHell

Reputation: 6006

You can do something like this:

select vehicle.*, (noOfSeat -  
  (select sum(seatBooked)
    from assigned
    where sid = 28
    and (assigned_start between '2018-01-06 02:01' and '2018-01-06 04:02' )
    and (assigned_end between '2018-01-06 02:01' and '2018-01-06 04:02')
  )) as seatsAvailable
from vehicle
having seatsAvailable > 0

http://sqlfiddle.com/#!9/c6bcb6/15

Upvotes: 2

Related Questions