1

So I have built a booking system, and I am having a few problems with some of the price calculations.

Originally I had

SELECT c_id, c_title, c_imgdrop, c_link, c_text4, 
    (SELECT SUM(bd_price) as price FROM booking_dates WHERE site_id = '15' AND bd_room_type IN ('single','double') AND bd_date IN ('2011-03-05') AND bd_h_id = c_id) AS sum_price
FROM c_content
JOIN (SELECT bd_h_id FROM booking_dates WHERE site_id = '15' AND bd_date IN ('2011-03-05') AND bd_available <= '1' AND bd_room_type = 'single' AND bd_price > '0' GROUP BY bd_h_id HAVING count(*) = '1' ) q_0 ON c_content.c_id = q_0.bd_h_id
JOIN (SELECT bd_h_id FROM booking_dates WHERE site_id = '15' AND bd_date IN ('2011-03-05') AND bd_available <= '1' AND bd_room_type = 'double' AND bd_price > '0' GROUP BY bd_h_id HAVING count(*) = '1' ) q_1 ON c_content.c_id = q_1.bd_h_id
WHERE site_id = '15'

Which worked fine, except it didnt work very well if you try to calculate multiple room types of the same type

Anyways I tried to split the queries out for room types, and multiple the value by the number of rooms for that type in the booking. I was using this ( Adding MySQL alias fields together ) which meant I had to add an extra level of sub queries which confused things a bit.

I am nearly there now, but its not recognising one of the fieldnames in my sub queries, I expect due to the order in which it is executing the query.

What I have now is:

SELECT c_id, c_title, c_imgdrop, c_link, c_text4, 
    (SELECT sum_price_single, sum_price_double, sum_price_single + sum_price_double AS sum_price FROM
        (SELECT
            ((SELECT SUM(bd_price) as price FROM booking_dates WHERE site_id = '15' AND bd_room_type = 'single' AND bd_date IN ('2011-03-05') AND bd_h_id = c_id) * 1) AS sum_price_single,
            ((SELECT SUM(bd_price) as price FROM booking_dates WHERE site_id = '15' AND bd_room_type = 'double' AND bd_date IN ('2011-03-05') AND bd_h_id = c_id) * 1) AS sum_price_double
            FROM booking_dates
        )
    x)
FROM c_content
JOIN (SELECT bd_h_id FROM booking_dates WHERE site_id = '15' AND bd_date IN ('2011-03-05') AND bd_available <= '1' AND bd_room_type = 'single' AND bd_price > '0' GROUP BY bd_h_id HAVING count(*) = '1' ) q_0 ON c_content.c_id = q_0.bd_h_id
JOIN (SELECT bd_h_id FROM booking_dates WHERE site_id = '15' AND bd_date IN ('2011-03-05') AND bd_available <= '1' AND bd_room_type = 'double' AND bd_price > '0' GROUP BY bd_h_id HAVING count(*) = '1' ) q_1 ON c_content.c_id = q_1.bd_h_id
WHERE site_id = '15'

And is giving me the following error

Unknown column 'c_id' in 'where clause'

Any ideas? This should output a list of hotels (values from c_content table), and a price for each one. The price consists of the SUM of various dates, but also has to take into account mtuliple rooms, and mixed room types which have different prices also.

Community
  • 1
  • 1
Horse
  • 3,023
  • 5
  • 38
  • 65

2 Answers2

1

Subselects are can be a performance killer, and should be avoided where possible. Nested sub-selects are many times more evil, and will cause scope problems similar to the one you're encountering. I'd recommend removing all of the sub-sub-selects, and if you have time, rewriting the query such that it requires no subselects at all.

In the meantime, something like the following should resolve your scoping problem, I think.

SELECT *, sum_price_single + sum_price_double AS sum_price FROM (
    SELECT c_id, c_title, c_imgdrop, c_link, c_text4,
        (SELECT SUM(bd_price) * 1 as price FROM booking_dates WHERE site_id = '15' AND bd_room_type = 'single' AND bd_date IN ('2011-03-05') AND bd_h_id = c_id) AS sum_price_single,
        (SELECT SUM(bd_price) * 1 as price FROM booking_dates WHERE site_id = '15' AND bd_room_type = 'double' AND bd_date IN ('2011-03-05') AND bd_h_id = c_id) AS sum_price_double
    FROM c_content
    JOIN (SELECT bd_h_id FROM booking_dates WHERE site_id = '15' AND bd_date IN ('2011-03-05') AND bd_available <= '1' AND bd_room_type = 'single' AND bd_price > '0' GROUP BY bd_h_id HAVING count(*) = '1' ) q_0 ON c_content.c_id = q_0.bd_h_id
    JOIN (SELECT bd_h_id FROM booking_dates WHERE site_id = '15' AND bd_date IN ('2011-03-05') AND bd_available <= '1' AND bd_room_type = 'double' AND bd_price > '0' GROUP BY bd_h_id HAVING count(*) = '1' ) q_1 ON c_content.c_id = q_1.bd_h_id
    WHERE site_id = '15'
) AS t1

Essentially moving the core subselects up two levels, and wrapping the "sum" of single and double into an outer select. To confirm, this isn't optimal ... but it should solve your immediate problem. I may revisit the problem after lunch :P

Jeff Parker
  • 7,367
  • 1
  • 22
  • 25
  • 1
    Nice one, I had already done as you said and solved the problem for now, however it meant getting rid of the aliases for the room type / price queries and just adding them together, which means I wouldnt be able to split out the room prices if I needed too (which the client literally just asked for). I prefer your solution :) Thx muchly! – Horse Mar 10 '11 at 12:51
0

Not the final solution, as prefer Jeff Parkers, but here is the query I used to solve it in the short term

SELECT c_id, c_title, c_imgdrop, c_link, c_text4,
((SELECT SUM(bd_price) as price FROM booking_dates WHERE site_id = '15' AND bd_room_type = 'single' AND bd_date IN ('2011-03-05') AND bd_h_id = c_id) * 1)
+
((SELECT SUM(bd_price) as price FROM booking_dates WHERE site_id = '15' AND bd_room_type = 'double' AND bd_date IN ('2011-03-05') AND bd_h_id = c_id) * 1) AS sum_price
FROM c_content
JOIN (SELECT bd_h_id FROM booking_dates WHERE site_id = '15' AND bd_date IN ('2011-03-05') AND bd_available <= '1' AND bd_room_type = 'single' AND bd_price > '0' GROUP BY bd_h_id HAVING count(*) = '1' ) q_0 ON c_content.c_id = q_0.bd_h_id
JOIN (SELECT bd_h_id FROM booking_dates WHERE site_id = '15' AND bd_date IN ('2011-03-05') AND bd_available <= '1' AND bd_room_type = 'double' AND bd_price > '0' GROUP BY bd_h_id HAVING count(*) = '1' ) q_1 ON c_content.c_id = q_1.bd_h_id
WHERE site_id = '15' ORDER BY sum_price desc
Horse
  • 3,023
  • 5
  • 38
  • 65