CODEWITHSUNDEEP
mysqlsqljoin
Rooshan Akthar
Rooshan Akthar

Reputation: 399

sql query to find available dates

I don't know this has a solution or I don't know which search term to use. This is my question. I have 4 Tables. Which are

I have been using below query to generate available dates. So someone can order for single date. Ex : 2015-12-17

SELECT DISTINCT
        *
FROM    Available AS a
        JOIN School_menu AS sm ON a.school_menu_id = sm.school_menu_id
        JOIN Menu AS m ON m.menu_id = sm.menu_id
        JOIN Item AS it ON m.menu_id = it.menu_id
WHERE   ( a.available_date BETWEEN '2015-12-27'
                           AND     '2016-02-05' )
        AND sm.sch_id = '7';

Recently, I have been told to add another feature. In detail,

Items,

if a user can order pizza on 12th of Dec. another press of a button should be able to order for the next few days in coming days. for 13th for 14th if Pizza is available for next few days queried through Available dates joined to menu. 1 itemis not a problem. what about 2 items. how do I write a query for that ? It didn't seem complicated on first. So, I've written a query

SELECT DISTINCT
        *
FROM    Available AS a
        JOIN School_menu AS sm ON a.school_menu_id = sm.school_menu_id
        JOIN Menu AS m ON m.menu_id = sm.menu_id
        JOIN Item AS it ON m.menu_id = it.menu_id
WHERE   ( a.available_date BETWEEN '2015-12-27'
                           AND     '2016-02-05' )
        AND sm.sch_id = '7'
        AND it.item_id IN ( 28, 31 )
        AND a.available_date != DATE(( '2016-01-05' ));

like this and messed up the whole ordering system.

How do I write the correct query !! Thanks

Added, SQL Fiddle http://sqlfiddle.com/#!9/3f44d/1

Upvotes: 0

Views: 101

Answers (1)

Rooshan Akthar
Rooshan Akthar

Reputation: 399

I was able to fix this query by Grouping Available dates together and calculating the count of the groups and dynamically adjusting the having count. 

SELECT DISTINCT *, COUNT(*) 
   FROM Available AS a JOIN School_menu AS sm ON 
     a.school_menu_id = sm.school_menu_id JOIN Menu AS m ON 
     m.menu_id = sm.menu_id JOIN Item AS it ON m.menu_id = it.menu_id 
WHERE (a.available_date BETWEEN '2015-12-27' AND '2016-02-05') 
 AND sm.sch_id = '7' AND it.item_id IN (26, 25, 28) 
 AND a.available_date != DATE(('2016-01-04')) GROUP BY a.available_date
HAVING COUNT (a.available_date) > XX
xx = Dyamic Count

Upvotes: 1

Related Questions