Webmaster Forum / ColdFusion / Advanced Techniques / August 2006

Hello I have this query in my cf program

First query
SELECT     TLA_PROP.PIN, TLA_PROP.ASSR_PRCL_NBR, TCASE.CASE_NBR,
TLA_PROP.STR_NBR, TLA_PROP.STR_NBR_RNG_END, TLA_PROP.STR_FRAC_NBR,
TLA_PROP.STR_FRAC_NBR_RNG_END, TLA_PROP.STR_DIR_CD, TLA_PROP.STR_NM,
TLA_PROP.STR_SFX_CD, TLA_PROP.STR_SFX_DIR_CD,
TLA_PROP.STR_UNIT_TYP_CD, TLA_PROP.UNIT_NBR, TLA_PROP.UNIT_NBR_RNG_END,
TLA_PROP.ZIP_CD, TLA_PROP.ZIP_CD_SFX, TLA_PROP.CNCL_DIST_NBR,
TLA_PROP.PLAN_AREA_NBR, TLA_PROP.ZONE_REG_CD, TAPLC.PROJ_DESC_TXT,
TCASE.CASE_ID, TCASE.CASE_NBR, taplc.aplc_id
FROM     TLA_PROP INNER JOIN ((TAPLC INNER JOIN TCASE ON TAPLC.APLC_ID =
TCASE.APLC_ID) INNER JOIN TLOC ON TAPLC.APLC_ID = TLOC.APLC_ID) ON
TLA_PROP.PROP_ID = TLOC.LOC_ID
WHERE     TCASE.CASE_NBR Like '%HPOZ%' and TCASE.CASE_NBR = '#form.caseNbr#'

'#form.caseNbr#' is the input field on my input box.

I am a beginner and I am not used to joins and joins in the from class

Second query
In toad I use this query
Select
    tcase.case_id
    ,Tcase.case_NBR
    ,tcntct.CNTCT_ID as ownerInformation
    ,tcntct.CO_BUSN_NM, tcntct.FRST_NM, tcntct. LST_NM
    ,tcntct_owner.cntct_id as repInformation
    ,tcntct_owner.CO_BUSN_NM
    , tcntct_owner. FRST_NM
    , tcntct_owner.LST_NM
    ,Tref_plan_area.plan_area_desc
    ,tcase_req.case_req_id
    ,tcase_req.case_req_typ_cd
    ,tcase_req.sys_user_id
    ,tla_prop.pin
    ,tla_prop.cncl_dist_nbr
    ,tla_prop.PLAN_AREA_NBR
    ,tla_prop.str_nbr
    ,tla_prop.STR_FRAC_NBR
    ,tla_prop.STR_DIR_CD
    ,tla_prop.str_nm
    ,tla_prop.STR_SFX_CD
    ,tla_prop.STR_SFX_DIR_CD
    ,tla_prop.STR_UNIT_TYP_CD
    ,tla_prop.UNIT_NBR
    ,tla_prop.ZIP_CD
    ,tla_prop.ASSR_PRCL_NBR
    ,TLA_PROP.ZONE_REG_CD
    from
    tloc, tla_prop , tcase, tref_plan_area, tcase_req,tcntct_aplc, tcntct,
tcntct_aplc tcntct_aplc_owner, tcntct tcntct_owner   /*tcntct_tla_prop,tcntct*/
   
    where
    tla_prop.prop_id = tloc.loc_id
    and
    tla_prop.plan_area_nbr = tref_plan_area .plan_area_NBR
    and
    tloc.aplc_id = tcase.aplc_id
    and
    tcase.case_id = tcase_req .case_id
    and
    tcase_req.CASE_req_typ_CD like '%HPOZ%'
/*

    and

    tla_prop.prop_id = tcntct_tla_prop.PROP_ID(+)

    and

    tcntct_tla_prop.CNTCT_ID = tcntct.CNTCT_ID(+)

*/

    and

    tcase.aplc_id = tcntct_aplc.APLC_ID(+)
    and

    tcntct_aplc.cntct_id = tcntct.cntct_id(+)
    and

    (tcntct_aplc.CNTCT_TYP_CD = 'A' or tcntct_aplc .cntct_typ_cd is null)
    and

    tcase.aplc_id = tcntct_aplc_owner .aplc_id(+)
    and

    tcntct_aplc_owner.cntct_id = tcntct_owner.cntct_id (+)
    and

    (tcntct_aplc_owner.cntct_typ_cd = 'R' or tcntct_aplc_owner.cntct_typ_cd is
null)
    /*

    group by

    tcase.case_id,

    Tcase.case_NBR,

    Tref_plan_area.plan_area_desc

    ,tcase_req.case_req_id

    ,tcase_req.case_req_typ_cd

    ,tcase_req.sys_user_id

    ,tla_prop.pin

    ,tla_prop.cncl_dist_nbr

    ,tla_prop.PLAN_AREA_NBR

    ,tla_prop.str_nbr

    ,tla_prop.STR_FRAC_NBR

    ,tla_prop.STR_DIR_CD

    ,tla_prop.str_nm

    ,tla_prop.STR_SFX_CD
   
    ,tla_prop.STR_SFX_DIR_CD

    ,tla_prop.STR_UNIT_TYP_CD

    ,tla_prop.UNIT_NBR

    ,tla_prop.ZIP_CD
   
    ,tla_prop.ASSR_PRCL_NBR,

    tcntct.CO_BUSN_NM
    , tcntct.FRST_NM
    , tcntct.LST_NM,

    tcntct_owner.CO_BUSN_NM   
    , tcntct_owner.FRST_NM
    , tcntct_owner.LST_NM

*/

order by

to get
Tref_plan_area.plan_area_desc or simply get plan_area_desc.  This is done
through a join tla_prop.plan_area_nbr = tref_plan_area .plan_area_NBR .

I am still using the first query ( the very first query above) but there are
joins in the from clause.
My question here is I am still trying to get plan_area_desc and want to
incorporate some syntax from the second query to the first query.  

What I did that was a mistake was ( error)

Added the table from and put this tla_prop.plan_area_nbr = tref_plan_area
.plan_area_NBR .
 In the where clause.  It did not work.  Can any one help me on how I can
still get the tref_plan_area .plan_area_NBR .

What do I add to the first query
Thanks