get to few rows in a left join query

[helenp]

Hi I am trying to redo a query as i have changed the calendar system,
I am trying to do this,
this dont give me errors but returns to few rows:

SELECT buscador.id_propiedad, calendar_table.property, minimo,
llegada, salida, propiedad, link_ingles, link_esp, link_sve, duermen FROM buscador 
LEFT JOIN calendar_table on buscador.id_propiedad = calendar_table.property 
LEFT JOIN bookings ON propiedad = buscador.id_propiedad WHERE (llegada <= '2012-01-01'
AND salida >= '2012-01-01' + INTERVAL 1
DAY
OR salida <= '2012-01-06'
AND llegada >= '2012-01-06' + INTERVAL -1
DAY or 
llegada <= '2012-01-01' AND salida >= '2012-01-06' or llegada >= '2012-01-01' 
AND salida <= '2012-01-06' AND 
propiedad is NULL and cal_date BETWEEN ('2012-01-01') AND ('2012-01-05')) 
group by buscador.id_propiedad

This returns me 14 rows when it should give me 23.
This query uses a form where one choose dates to see if available, and if availble I should get some information of the properties available.
There are 3 table involved:
1. table booking where all the bookings are inserted so if the query returns a null value that means its available, and the bookings are inserted using column llegada and salida.
2. table buscador, its only to get information such as links, max capacity etc.
3. table calendar_table, is where the dates with its daily price are inserted, in this case I dont calculate price however I use it to only get actual properties and also some information as minimum etc.

The original query is this, uses another table :

SELECT buscador.id_propiedad, precios.id_propiedad, minimo,
llegada, salida, propiedad, link_ingles, link_esp, link_sve, duermen FROM buscador 
LEFT JOIN precios on buscador.id_propiedad = precios.id_propiedad 
LEFT JOIN bookings ON propiedad = buscador.id_propiedad AND
(('2012-01-01' BETWEEN llegada AND date_sub(salida, interval +1 day)) 
or ('2012-01-05' BETWEEN date_sub(llegada, interval -1 day) AND salida) 
or (llegada <= '2012-01-01' AND salida >= '2012-01-05') 
or (llegada >= '2012-01-01' AND salida <= '2012-01-05')) WHERE  
propiedad is NULL order by buscador.id_propiedad

this returns 23 rows wich is correct.

Cant see what is wrong, any ideas?

[helenp]

Hm, just saw why, it only gives me 14 as it gives me the properties that are not available instead of the available properties.

Using the same way of doing even though its not the best way it gives me 23 rows:

SELECT buscador.id_propiedad, calendar_table.property, minimo, llegada, salida, propiedad, link_ingles, link_esp, link_sve, duermen
FROM buscador
LEFT JOIN calendar_table ON buscador.id_propiedad = calendar_table.property
LEFT JOIN bookings ON propiedad = buscador.id_propiedad
AND (('2012-01-01'
BETWEEN llegada
AND date_sub( salida, INTERVAL +1
DAY ))
OR ('2012-01-05'
BETWEEN date_sub( llegada, INTERVAL -1
DAY )
AND salida)
OR (
llegada <= '2012-01-01'
AND salida >= '2012-01-05')
OR (llegada >= '2012-01-01'
AND salida <= '2012-01-05'))
WHERE propiedad IS NULL
AND cal_date
BETWEEN (
'2012-01-01')
AND ('2012-01-05')
GROUP BY buscador.id_propiedad

[helenp]

Solved it:

SELECT buscador.id_propiedad, calendar_table.property, minimo,
llegada, salida, propiedad, link_ingles, link_esp, link_sve, duermen FROM buscador 
LEFT JOIN calendar_table on buscador.id_propiedad = calendar_table.property 
LEFT JOIN bookings ON propiedad = buscador.id_propiedad AND (llegada <= '2012-01-01'
AND salida >= '2012-01-01' + INTERVAL 1
DAY
OR salida <= '2012-01-06'
AND llegada >= '2012-01-06' + INTERVAL -1
DAY or 
llegada <= '2012-01-01' AND salida >= '2012-01-06' or llegada >= '2012-01-01' 
AND salida <= '2012-01-06') WHERE 
propiedad is NULL and cal_date BETWEEN ('2012-01-01') AND ('2012-01-05') 
group by buscador.id_propiedad