SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member bicho44's Avatar
    Join Date
    Dec 2002
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Real Estate Database problem

    Hi:

    I have a tricky one (at least for me)

    I have 3 tables but one of them its just for reference data.

    My site its a Real Estate one who rent houses for tourism, so i was making a "search engine" to obtain the propertys who dont have booked in between two dates. But i cannot make it work.

    Here's my code:

    Code:
    SELECT p.*, d.* , r.date_in, r.date_out 
    FROM propiedades AS p, duenos AS d, reservas AS r 
    WHERE p.propietario_id = d.propietario_id 
    AND r.propiedad_id = p.propiedad_id 
    AND LOWER(p.activa)='s' 
    AND LOWER(p.auto)!='s' 
    AND LOWER(p.tipo)='a' 
    AND p.localidad_id = 1 
    AND p.capacidad_max>=3 
    AND r.confirmada='s' 
    AND DATE(r.date_in) >= DATE('2008-08-02') 
    AND DATE(r.date_out) NOT BETWEEN DATE('2008-07-27') AND DATE('2008-08-02') 
    AND DATE(r.date_in) NOT BETWEEN DATE('2008-07-27') AND DATE('2008-08-02')
    GROUP by p.propiedad_id 
    ORDER BY p.value DESC
    Yes im a bit clumsy in my code (ok a lot). but the "First part" seems to work the problem its in the DATE part.

    Table description:
    Propiedades as p
    are the Properties
    Duenos as d are the owners
    Reservas as r are the booking one

    Simplify
    I need the properties who dosent have a booked date between two dates

    I hope everyone understand.

    Thx in advance
    Regards From Patagonia

    Fede

  2. #2
    SitePoint Member bicho44's Avatar
    Join Date
    Dec 2002
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi:

    I think i gotit

    Code:
    SELECT p.*, r.fecha_entrada, r.fecha_salida 
    FROM propiedades AS p
    JOIN duenos as d
    ON p.propietario_id = d.propietario_id
    LEFT JOIN reservas AS r 
    ON r.propiedad_id = p.propiedad_id 
    AND YEAR (r.fecha_entrada) >= YEAR ('2008-06-01')
    AND not (date(r.fecha_entrada) between ('2008-06-01') and ('2008-06-20')
    AND DATE(r.fecha_salida) between ('2008-06-01') and ('2008-06-20'))
    WHERE LOWER(p.activa)='s' 
    AND LOWER(p.auto)!='s' 
    AND LOWER(p.tipo)='a' 
    AND p.localidad_id = 1 
    AND p.capacidad_max>=3
    AND r.fecha_entrada IS NULL
    GROUP BY p.propiedad_id
    ORDER BY p.precio ASC
    If someone found an error in that, and its so kind to tell me, i apreciate.

    Again Thx in advance
    Regards From Patagonia

    Fede


Bookmarks

Posting Permissions

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