CODEWITHSUNDEEP
phpmysqlselectleft-join
David G
David G

Reputation: 301

MYSQL select query with multiple tables

I have 2 tables,

  1. prices (pcode, date, priceperweek)
    • 1 record covers 1 week
  2. booked (pcode, date)
    • 1 record = 1 day, because shortbreaks are available

And a form consists of 3 fields

  1. startdate
  2. flexibility (+/- 1/2/3 weeks)
  3. duration (3 / 4 / 5 / 6 / 7 / 14)).

The below query should be selecting prices & dates and checking to see if the selected start date appears in the prices table and that each day from the startdate doesn't appear in the bookings table.

SELECT SUM(priceperweek) AS `ppw`, prices.date AS `startdate`
FROM `prices` LEFT JOIN `booked` ON prices.pcode=booked.pcode
WHERE prices.pcode='A2CD59GH'
    AND (prices.date IN ('20131221', '20131228')
        AND booked.date NOT IN ('20131221', '20131222', '20131223',
        '20131224', '20131225', '20131226', '20131227', '20131228',
        '20131229', '20131230', '20131231', '20140101', '20140102',
        '20140103')
    )
    OR (prices.date IN ('20131214', '20131221')
        AND booked.date NOT IN ('20131214', '20131215', '20131216',
            '20131217',
            '20131218', '20131219', '20131220', '20131221', '20131222',
            '20131223', '20131224', '20131225', '20131226', '20131227')
    )
    OR (prices.date IN ('20131228', '20140104') AND booked.date NOT IN
        ('20131228', '20131229', '20131230', '20131231', '20140101',
        '20140102', '20140103', '20140104', '20140105', '20140106',
        '20140107', '20140108', '20140109', '20140110')
    )
GROUP BY prices.date
ORDER BY prices.date ASC

VALUES GIVEN TO QUERY...

My problem is that this query returns records even if some of the dates in a range appear in the "bookings" table AND the ppw value is alot more than i would have expected.

The reason for using SUM(ppw) is when a duration of 14 is specified the price will sum both weeks together.

Thanks for any help on this

Upvotes: 0

Views: 238

Answers (1)

eggyal
eggyal

Reputation: 125835

The problem with your approach is that the startdate will only be filtered from the results if every single record in the booked table for the given pcode falls within the booking period. Obviously this won't be the case if the property has been booked on some other date.

I'd suggest performing an anti-join along the following lines:

SELECT   t.date, SUM(prices.priceperweek) FROM prices JOIN (
  SELECT   prices.date
  FROM     prices LEFT JOIN booked
              ON booked.pcode = prices.pcode
             AND booked.date BETWEEN prices.date
                                 AND prices.date + INTERVAL 14 DAY
  WHERE    booked.pcode IS NULL
       AND prices.pcode = 'A2CD59GH'
       AND prices.date BETWEEN '20131221' - INTERVAL 1 WEEK
                           AND '20131221' + INTERVAL 2 WEEK
) t ON prices.date BETWEEN t.date AND t.date + INTERVAL 13 DAY
GROUP BY t.date

Upvotes: 1

Related Questions