example.sql

CREATE TABLE users (
                      user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '사용자 ID',
                      email VARCHAR(32) NOT NULL UNIQUE COMMENT '이메일',
                      name VARCHAR(16) NOT NULL COMMENT '이름',
                      nickname VARCHAR(16) NOT NULL COMMENT '닉네임',
                      password VARCHAR(100) NOT NULL COMMENT '비밀번호',
                      address VARCHAR(50) COMMENT '주소',
                      additional_point INT DEFAULT NULL COMMENT '가점'
);
SELECT * FROM users;


show databases;

use MyHomeCatch;
show tables;
select pan_id from LH_notice;

SELECT
    -- Officetel basic info (aliased with O_)
    O.PBLANC_NO AS O_PBLANC_NO,
    O.BSNS_MBY_NM AS O_BSNS_MBY_NM,
    O.CNTRCT_CNCLS_BGNDE AS O_CNTRCT_CNCLS_BGNDE,
    O.CNTRCT_CNCLS_ENDDE AS O_CNTRCT_CNCLS_ENDDE,
    O.HMPG_ADRES AS O_HMPG_ADRES,
    O.HOUSE_DTL_SECD AS O_HOUSE_DTL_SECD,
    O.HOUSE_DTL_SECD_NM AS O_HOUSE_DTL_SECD_NM,
    O.HOUSE_MANAGE_NO AS O_HOUSE_MANAGE_NO,
    O.HOUSE_NM AS O_HOUSE_NM,
    O.HOUSE_SECD AS O_HOUSE_SECD,
    O.HOUSE_SECD_NM AS O_HOUSE_SECD_NM,
    O.HSSPLY_ADRES AS O_HSSPLY_ADRES,
    O.HSSPLY_ZIP AS O_HSSPLY_ZIP,
    O.MDHS_TELNO AS O_MDHS_TELNO,
    O.MVN_PREARNGE_YM AS O_MVN_PREARNGE_YM,
    O.NSPRC_NM AS O_NSPRC_NM,
    O.PBLANC_URL AS O_PBLANC_URL,
    O.PRZWNER_PRESNATN_DE AS O_PRZWNER_PRESNATN_DE,
    O.RCRIT_PBLANC_DE AS O_RCRIT_PBLANC_DE,
    O.SEARCH_HOUSE_SECD AS O_SEARCH_HOUSE_SECD,
    O.SUBSCRPT_AREA_CODE AS O_SUBSCRPT_AREA_CODE,
    O.SUBSCRPT_AREA_CODE_NM AS O_SUBSCRPT_AREA_CODE_NM,
    O.SUBSCRPT_RCEPT_BGNDE AS O_SUBSCRPT_RCEPT_BGNDE,
    O.SUBSCRPT_RCEPT_ENDDE AS O_SUBSCRPT_RCEPT_ENDDE,
    O.TOT_SUPLY_HSHLDCO AS O_TOT_SUPLY_HSHLDCO,

    -- Officetel model info (aliased with M_)
    M.MODEL_UID AS M_MODEL_UID,
    M.EXCLUSE_AR AS M_EXCLUSE_AR,
    M.GP AS M_GP,
    M.HOUSE_MANAGE_NO AS M_HOUSE_MANAGE_NO,
    M.MODEL_NO AS M_MODEL_NO,
    M.PBLANC_NO AS M_PBLANC_NO,
    M.SUBSCRPT_REQST_AMOUNT AS M_SUBSCRPT_REQST_AMOUNT,
    M.SUPLY_AMOUNT AS M_SUPLY_AMOUNT,
    M.SUPLY_HSHLDCO AS M_SUPLY_HSHLDCO,
    M.TP AS M_TP,

    -- Officetel competition info (aliased with C_)
    C.CMPET_UID AS C_CMPET_UID,
    C.CMPET_RATE AS C_CMPET_RATE,
    C.HOUSE_MANAGE_NO AS C_HOUSE_MANAGE_NO,
    C.HOUSE_TY AS C_HOUSE_TY,
    C.MODEL_NO AS C_MODEL_NO,
    C.PBLANC_NO AS C_PBLANC_NO,
    C.REQ_CNT AS C_REQ_CNT,
    C.RESIDNT_PRIOR_AT AS C_RESIDNT_PRIOR_AT,
    C.RESIDNT_PRIOR_SENM AS C_RESIDNT_PRIOR_SEMN,
    C.SUPLY_HSHLDCO AS C_SUPLY_HSHLDCO
FROM APPLYHOME_officetel O
         LEFT JOIN APPLYHOME_officetel_model M ON O.PBLANC_NO = M.PBLANC_NO
         LEFT JOIN APPLYHOME_officetel_cmpet C ON O.PBLANC_NO = C.PBLANC_NO AND M.MODEL_NO = C.MODEL_NO
WHERE O.PBLANC_NO = 2025950035
ORDER BY O.PBLANC_NO, M.MODEL_NO, C.RESIDNT_PRIOR_AT DESC;

show tables;
select * from APPLYHOME_officetel where PBLANC_NO in (select pan_id from LH_notice);