0

Here i am using JOIN in 3 tables and getting the count.

  1. trip_details
  2. escort_allocation
  3. cab_allocation

cab_allocation i am taking Total_Count and escort_allocation i am taking Escort_Count like trip_details allocationId i am using forien key escort_allocation using inner join i am taking count.Adhoc_Trip_Count i am taking from trip_details and cab_allocation.trip_details allocationId i am using forien key cab_allocation using this i am taking count ,my query is working perfect.Now my question is i want to add BETWEEN in two dates.in trip_details table i have one column called tripDate.suppose i am giving dates like 2018-03-20 INTO 2018-03-23 means , i want fetch records between this two dates

MYSQL QUERY

SELECT COUNT(T.tripId) as Escort_Count,
  (
      SELECT COUNT(*) FROM 
      (
          SELECT a.allocationId
          FROM escort_allocation a 
          INNER JOIN cab_allocation c ON a.allocationId = c.allocationId 
          WHERE c.allocationType = 'Adhoc Trip' 
          GROUP BY a.allocationId
      ) AS Ad

  ) AS Adhoc_Trip_Count,
  (SELECT COUNT(id) FROM trip_details) as Total_Count
  FROM 
  ( 
      SELECT a.tripId FROM 
      trip_details a 
      INNER JOIN 
      escort_allocation b 
      ON a.allocationId = b.allocationId 
      GROUP BY a.allocationId 
  ) AS T

SQL FIDDLE

Kani R
  • 123
  • 6
  • add the date column to the table add some data into that column, then tell us what the expected result should be. You have 2 subqueries referencing trip_details, should both be affected by the date range? your question isn't clear enough to answer well. – Paul Maxwell Mar 23 '18 at 04:10
  • This looks like the exact same question as https://stackoverflow.com/questions/49434096/how-to-use-between-condition-using-using-main-query-and-sub-queries – Barmar Mar 23 '18 at 04:32

0 Answers0