3 Replies Latest reply on Sep 14, 2006 7:34 AM by Coldfusionstudent

    join/from/where/ combine queries 3

    Coldfusionstudent Level 1
      hello my query below is not working for my cf application can you help?
      thanks


      this is my previous question so i tried to do it my self.


      tcase_req.tcase_req_id is added tcase.case_id=tcase_req.case_id :note that this is not part of the inner join in the from clause. it does not depend on the rest of the inner join.

      the reaon for this is that i need to get
      WHERE tcase_req.case_req_typ_cd = cwc and tcase_req.case_req_typ_cd = cwnc

      here is the original question

      Hello ,
      I need help again on the inner join/and conditions in the where clause

      I have these 2 tables tcase and tcase_req where there common field Is the case_id.
      tcase is the parent table and the tcase_req is the child table.

      (1)I wanted to add this to the from clause using the inner join table . what do I do and where do I put it

      (2)I then need to put a condition in the where clause to replace
      WHERE TCASE.CASE_NBR Like '%HPOZ%'
      AND TCASE.CASE_NBR = 'DIR-2004-4269-HPOZ-CCMP'
      by
      WHERE TCASE.CASE_NBR Like '%CWC%' or TCASE.CASE _NBR Like '%CWNC%'
      AND TCASE.CASE_NBR = 'DIR-2004-4269-CWC'
      Or TCASE.CASE_NBR = 'DIR-2004-4269-CWNC'

      is this efficient??



      thanks
      --------------------

      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,
      Tref_plan_area.plan_area_desc,
      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,
      tcase_req.case_req_typ_cd
      FROM TLA_PROP INNER JOIN tref_plan_area ON tla_prop.plan_area_nbr = tref_plan_area.plan_area_NBR INNER JOIN TLOC ON TLA_PROP.PROP_ID = TLOC.LOC_ID INNER JOIN TAPLC ON TLOC.APLC_ID = TAPLC.APLC_ID INNER JOIN TCASE ON TAPLC.APLC_ID = TCASE.APLC_ID
      WHERE TCASE.CASE_NBR Like '%CWC%'and tcase.case_id=tcase_req.case_id




      (3)To a tcase_req
      Suffix_id are equal to = cwc and cwnc ( in the tcase_req table)(Suffix_id is the field that cintains the suffix cwc and cwnc for the tcase_req)

      also,
      this is the original query and it works fine
      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,
      Tref_plan_area.plan_area_desc,
      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 tref_plan_area ON tla_prop.plan_area_nbr = tref_plan_area.plan_area_NBR INNER JOIN TLOC ON TLA_PROP.PROP_ID = TLOC.LOC_ID INNER JOIN TAPLC ON TLOC.APLC_ID = TAPLC.APLC_ID INNER JOIN TCASE ON TAPLC.APLC_ID = TCASE.APLC_ID
      WHERE (TCASE.CASE_NBR Like '%CWC%' or TCASE.CASE_NBR Like '%CWNC%')
        • 1. Re: join/from/where/ combine queries 3
          Level 7
          Your From statement would be this.

          FROM tcase_req INNER JOIN tcase ON tcase_req.case_id = tcase.case_id


          "Coldfusionstudent" <webforumsuser@macromedia.com> wrote in message
          news:ee6klf$969$1@forums.macromedia.com...
          > hello my query below is not working for my cf application can you help?
          > thanks
          >
          >
          > this is my previous question so i tried to do it my self.
          >
          >
          > tcase_req.tcase_req_id is added tcase.case_id=tcase_req.case_id :note
          > that
          > this is not part of the inner join in the from clause. it does not depend
          > on
          > the rest of the inner join.
          >
          > the reaon for this is that i need to get
          > WHERE tcase_req.case_req_typ_cd = cwc and tcase_req.case_req_typ_cd =
          > cwnc
          >
          > here is the original question
          >
          > Hello ,
          > I need help again on the inner join/and conditions in the where clause
          >
          > I have these 2 tables tcase and tcase_req where there common field Is the
          > case_id.
          > tcase is the parent table and the tcase_req is the child table.
          >
          > (1)I wanted to add this to the from clause using the inner join table .
          > what
          > do I do and where do I put it
          >
          > (2)I then need to put a condition in the where clause to replace
          > WHERE TCASE.CASE_NBR Like '%HPOZ%'
          > AND TCASE.CASE_NBR = 'DIR-2004-4269-HPOZ-CCMP'
          > by
          > WHERE TCASE.CASE_NBR Like '%CWC%' or TCASE.CASE _NBR Like '%CWNC%'
          > AND TCASE.CASE_NBR = 'DIR-2004-4269-CWC'
          > Or TCASE.CASE_NBR = 'DIR-2004-4269-CWNC'
          >
          > is this efficient??
          >
          >
          >
          > thanks
          > --------------------
          >
          > 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,
          > Tref_plan_area.plan_area_desc,
          > 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,
          > tcase_req.case_req_typ_cd
          > FROM TLA_PROP INNER JOIN tref_plan_area ON tla_prop.plan_area_nbr =
          > tref_plan_area.plan_area_NBR INNER JOIN TLOC ON TLA_PROP.PROP_ID =
          > TLOC.LOC_ID
          > INNER JOIN TAPLC ON TLOC.APLC_ID = TAPLC.APLC_ID INNER JOIN TCASE ON
          > TAPLC.APLC_ID = TCASE.APLC_ID
          > WHERE TCASE.CASE_NBR Like '%CWC%'and tcase.case_id=tcase_req.case_id
          >
          >
          >
          >
          > (3)To a tcase_req
          > Suffix_id are equal to = cwc and cwnc ( in the tcase_req table)(Suffix_id
          > is
          > the field that cintains the suffix cwc and cwnc for the tcase_req)
          >
          > also,
          > this is the original query and it works fine
          > 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,
          > Tref_plan_area.plan_area_desc,
          > 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 tref_plan_area ON tla_prop.plan_area_nbr =
          > tref_plan_area.plan_area_NBR INNER JOIN TLOC ON TLA_PROP.PROP_ID =
          > TLOC.LOC_ID
          > INNER JOIN TAPLC ON TLOC.APLC_ID = TAPLC.APLC_ID INNER JOIN TCASE ON
          > TAPLC.APLC_ID = TCASE.APLC_ID
          > WHERE (TCASE.CASE_NBR Like '%CWC%' or TCASE.CASE_NBR Like '%CWNC%')
          >


          • 2. Re: join/from/where/ combine queries 3
            Level 7
            For guys like us that are not writing queries everyday you might try a query
            builder like the one MS Access has.


            "Coldfusionstudent" <webforumsuser@macromedia.com> wrote in message
            news:ee6klf$969$1@forums.macromedia.com...
            > hello my query below is not working for my cf application can you help?
            > thanks
            >
            >
            > this is my previous question so i tried to do it my self.
            >
            >
            > tcase_req.tcase_req_id is added tcase.case_id=tcase_req.case_id :note
            > that
            > this is not part of the inner join in the from clause. it does not depend
            > on
            > the rest of the inner join.
            >
            > the reaon for this is that i need to get
            > WHERE tcase_req.case_req_typ_cd = cwc and tcase_req.case_req_typ_cd =
            > cwnc
            >
            > here is the original question
            >
            > Hello ,
            > I need help again on the inner join/and conditions in the where clause
            >
            > I have these 2 tables tcase and tcase_req where there common field Is the
            > case_id.
            > tcase is the parent table and the tcase_req is the child table.
            >
            > (1)I wanted to add this to the from clause using the inner join table .
            > what
            > do I do and where do I put it
            >
            > (2)I then need to put a condition in the where clause to replace
            > WHERE TCASE.CASE_NBR Like '%HPOZ%'
            > AND TCASE.CASE_NBR = 'DIR-2004-4269-HPOZ-CCMP'
            > by
            > WHERE TCASE.CASE_NBR Like '%CWC%' or TCASE.CASE _NBR Like '%CWNC%'
            > AND TCASE.CASE_NBR = 'DIR-2004-4269-CWC'
            > Or TCASE.CASE_NBR = 'DIR-2004-4269-CWNC'
            >
            > is this efficient??
            >
            >
            >
            > thanks
            > --------------------
            >
            > 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,
            > Tref_plan_area.plan_area_desc,
            > 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,
            > tcase_req.case_req_typ_cd
            > FROM TLA_PROP INNER JOIN tref_plan_area ON tla_prop.plan_area_nbr =
            > tref_plan_area.plan_area_NBR INNER JOIN TLOC ON TLA_PROP.PROP_ID =
            > TLOC.LOC_ID
            > INNER JOIN TAPLC ON TLOC.APLC_ID = TAPLC.APLC_ID INNER JOIN TCASE ON
            > TAPLC.APLC_ID = TCASE.APLC_ID
            > WHERE TCASE.CASE_NBR Like '%CWC%'and tcase.case_id=tcase_req.case_id
            >
            >
            >
            >
            > (3)To a tcase_req
            > Suffix_id are equal to = cwc and cwnc ( in the tcase_req table)(Suffix_id
            > is
            > the field that cintains the suffix cwc and cwnc for the tcase_req)
            >
            > also,
            > this is the original query and it works fine
            > 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,
            > Tref_plan_area.plan_area_desc,
            > 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 tref_plan_area ON tla_prop.plan_area_nbr =
            > tref_plan_area.plan_area_NBR INNER JOIN TLOC ON TLA_PROP.PROP_ID =
            > TLOC.LOC_ID
            > INNER JOIN TAPLC ON TLOC.APLC_ID = TAPLC.APLC_ID INNER JOIN TCASE ON
            > TAPLC.APLC_ID = TCASE.APLC_ID
            > WHERE (TCASE.CASE_NBR Like '%CWC%' or TCASE.CASE_NBR Like '%CWNC%')
            >


            • 3. Re: join/from/where/ combine queries 3
              Coldfusionstudent Level 1
              thanks !
              like this :

              FROM
              TLA_PROP INNER JOIN tref_plan_area ON tla_prop.plan_area_nbr = tref_plan_area.plan_area_NBR INNER JOIN TLOC ON TLA_PROP.PROP_ID = TLOC.LOC_ID INNER JOIN TAPLC ON TLOC.APLC_ID = TAPLC.APLC_ID INNER JOIN TCASE ON TAPLC.APLC_ID = TCASE.APLC_ID tcase_req INNER JOIN tcase ON tcase_req.case_id = tcase.case_id


              WHERE (TCASE.CASE_NBR Like '%CWC%' or TCASE.CASE_NBR Like '%CWNC%')

              --------------------------------------------------------------------------------
              or like this below:


              FROM
              tcase_req INNER JOIN tcase ON tcase_req.case_id = tcase.case_id TLA_PROP INNER JOIN tref_plan_area ON tla_prop.plan_area_nbr = tref_plan_area.plan_area_NBR INNER JOIN TLOC ON TLA_PROP.PROP_ID = TLOC.LOC_ID INNER JOIN TAPLC ON TLOC.APLC_ID = TAPLC.APLC_ID INNER JOIN TCASE ON TAPLC.APLC_ID = TCASE.APLC_ID

              WHERE (TCASE.CASE_NBR Like '%CWC%' or TCASE.CASE_NBR Like '%CWNC%')