Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Oct 2011
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts

    get to few rows in a left join query

    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:
    Code:
    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 :
    Code:
    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?

  • #2
    New Coder
    Join Date
    Oct 2011
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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:

    Code:
    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
    Last edited by helenp; 01-09-2012 at 11:02 AM.

  • #3
    New Coder
    Join Date
    Oct 2011
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Solved it:

    Code:
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •