SELECT b.bukken_no, b.bukken_name, b.bukken_name2, cast(b.bukken_name_kana2 as int4) as list_number, b.new_web_yachin_ss, b.new_web_yachin_s, b.new_web_yachin_m, b.new_web_yachin_l, b.new_web_madori, b.new_web_madori_kara, b.new_web_hirosa, b.new_web_hirosa_kara, b.banchi, b.web_nyukyo_ninzu, b.new_web_list_bikou, b.walk1 AS walk1, b.walk2 AS walk2, b.walk3 AS walk3, b.car_access, b.bus1, b.bus2, b.bus3, b.bus_time1, b.bus_time2, b.bus_time3, b.bukken_hyouka, b.address_open_flg, b.jyouken1, b.jyouken11, b.new_web_setsubi, b.web_nyukyo_jyoukyou, b.parking, b.kenchiku_y, a.todoufuken, a.sikuchouson, a.choumei, a.choumoku, r1.name AS r1_name, e1.name AS e1_name, r2.name AS r2_name, e2.name AS e2_name, r3.name AS r3_name, e3.name AS e3_name, g.web_pay1, g.web_pay2, g.web_pay3, g.web_pay4, g.web_pay5, c.open_flg AS c_open_flg, ba.text1, ba.text1_comment, -- 過去 ( SELECT -- 期間確定か退去確認フラグが立っている場合、即入居とする MAX((CASE WHEN keiyaku = 1 OR taikyo_kakunin_flg = 1 THEN taikyo_day ELSE NULL END)) FROM torihiki WHERE bukken_no = b.bukken_no AND gyousha_cd = '7077' AND taikyo_day < CURRENT_DATE AND EXISTS( SELECT * FROM heya h WHERE torihiki.room_kanri_no = h.room_kanri_no AND h.gyousha_cd = '7077' AND h.kadou_flg <> '1' AND h.koukai_day <= CURRENT_DATE AND h.koukai_day_f >= CURRENT_DATE ) -- 部屋の一番最後の契約だけ見る AND EXISTS(SELECT * FROM (SELECT room_kanri_no, MAX(taikyo_day) AS max_taikyo_day FROM torihiki WHERE torihiki.bukken_no = b.bukken_no AND gyousha_cd = '7077' GROUP BY room_kanri_no) AS t1 WHERE t1.room_kanri_no = torihiki.room_kanri_no AND t1.max_taikyo_day = torihiki.taikyo_day) ) AS taikyo_kako, -- 未来 ( SELECT taikyo_day FROM torihiki WHERE bukken_no = b.bukken_no AND gyousha_cd = '7077' AND CURRENT_DATE <= taikyo_day -- 期間確定か退去確認フラグが立っている契約 AND (keiyaku = 1 OR taikyo_kakunin_flg = 1) -- 部屋の一番最後の契約だけ見る AND taikyo_day IN (SELECT MAX(taikyo_day) FROM torihiki WHERE torihiki.bukken_no = b.bukken_no AND gyousha_cd = '7077' GROUP BY room_kanri_no) ORDER BY taikyo_day , (CASE WHEN keiyaku = 1 OR taikyo_kakunin_flg = 1 THEN 0 ELSE 1 END) LIMIT 1 ) AS taikyo_mirai, -- 満室or即入居 ( SELECT MIN(CASE WHEN keiyaku != 1 AND taikyo_kakunin_flg != 1 THEN 1 ELSE 0 END ) FROM torihiki WHERE bukken_no = b.bukken_no AND gyousha_cd = '7077' AND EXISTS( SELECT * FROM heya h WHERE torihiki.room_kanri_no = h.room_kanri_no AND h.gyousha_cd = '7077' AND h.kadou_flg <> '1' AND h.koukai_day <= CURRENT_DATE AND COALESCE(h.koukai_day_f,'2050-01-01') >= CURRENT_DATE ) -- 期間確定ではない、かつ退去確認フラグが立っていない契約 --AND keiyaku != 1 AND taikyo_kakunin_flg != 1 -- 部屋の一番最後の契約だけ見る AND taikyo_day IN (SELECT MAX(taikyo_day) FROM torihiki WHERE torihiki.bukken_no = b.bukken_no AND gyousha_cd = '7077' GROUP BY room_kanri_no) ) AS keiyaku_ari FROM bukken AS b left outer join bukken_additional_data ba on b.bukken_no = ba.bukken_no LEFT OUTER JOIN address AS a ON b.address_cd = jyuusho_cd LEFT OUTER JOIN rosen AS r1 ON b.ensen_cd1 = r1.id LEFT OUTER JOIN eki AS e1 ON b.eki_cd1 = e1.id LEFT OUTER JOIN rosen AS r2 ON b.ensen_cd2 = r2.id LEFT OUTER JOIN eki AS e2 ON b.eki_cd2 = e2.id LEFT OUTER JOIN rosen AS r3 ON b.ensen_cd3 = r3.id LEFT OUTER JOIN eki AS e3 ON b.eki_cd3 = e3.id LEFT OUTER JOIN gyousha AS g ON b.gyousha_cd = g.gyousha_cd LEFT OUTER JOIN rosen_eki AS re1 ON (b.ensen_cd1 = re1.rosen_id and b.eki_cd1 = re1.eki_id) LEFT OUTER JOIN rosen_eki AS re2 ON (b.ensen_cd2 = re2.rosen_id and b.eki_cd2 = re2.eki_id) LEFT OUTER JOIN rosen_eki AS re3 ON (b.ensen_cd3 = re3.rosen_id and b.eki_cd3 = re3.eki_id) LEFT OUTER JOIN (SELECT bukken_no,min(open_flg) as open_flg FROM campaign WHERE open_flg = 0 GROUP BY bukken_no) AS c ON b.bukken_no = c.bukken_no WHERE b.open_flg <> 1 AND b.gyousha_cd = '7077' ORDER BY list_number,ensen_cd1,eki_cd1,walk1 - bus_time1 マンスリーマンション　東京／東京　港区でマンスリーマンションを選ぶならアーバンホームズ ｜検索結果

物件一覧

絞り込み検索 絞り込み検索

お部屋タイプ

[ 検索条件 : なし 81件]