locked
query formation RRS feed

  • Question

  • User364480375 posted

    hi

    guys

    I have to add GROUP_NAME in below query i.e GROUP_NAME should be displayed.
    column of GROUPS  are as follows:
    GROUP_NAME
    ID
    GROUP_USERS
    LEDIT

    query are as follows:

    "select 0, disp_office_code as office_code,(select office_name from offices where office_code=disp_office_code)
    as office_name from disp_offices where office_code='" & office98 & "' and disp_office_code in
     (select office_code from offices where office_level<>'STOP') order by id"




    table structure as follows:
    1.DISP_OFFICES
      ID    NUMBER(11,0)
      OFFICE_CODE    VARCHAR2(20 BYTE)
      DISP_OFFICE_CODE    VARCHAR2(20 BYTE)
      DISP_OFFICE_NAME    VARCHAR2(70 BYTE)

    2.OFFICES
     OFFICE_CODE    VARCHAR2(20 BYTE)
     OFFICE_NAME    VARCHAR2(70 BYTE)
      DEPARTMENT_CODE    VARCHAR2(20 BYTE)
      OFFICE_LEVEL    VARCHAR2(50 BYTE)
      ID    NUMBER(11,0)
      LEDIT    VARCHAR2(100 BYTE)

     above query is displaying correct result.I just want to display GROUP_NAME along with correct result.

    Tuesday, February 11, 2014 12:27 AM

Answers

  • User79986525 posted

    Hi ,

    Try this 

    select 0, DO.disp_office_code as office_code,O.office_name ,G.GROUP_NAME 
    FROM  disp_offices DO, offices O,Groups G 
    WHERE  O.office_code=DO.disp_office_code AND office_code='" & office98 & "' and disp_office_code in
     (select office_code from offices where office_level<>'STOP') AND G.LEDIT=O.LEDIT  order by id"

    Hope this will help you out ..

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 11, 2014 1:38 AM
  • User-2007877324 posted

    First let's remove the unnecessary subqueries from your select statement. You query can be rewritten as below

    select 0, disp_office_code as office_code, office_name
    from disp_offices d
    inner join offices o on o.office_code = d.disp_office_code
    where o.office_level<>'STOP' order by id

    Here is the query with join to groups

    select 0, disp_office_code as office_code, office_name, group_name
    from disp_offices d
    inner join offices o on o.office_code = d.disp_office_code
    inner join Groups g on g.ID = d.ID
    where o.office_level<>'STOP' order by id

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 11, 2014 4:16 AM

All replies

  • User364480375 posted

    ques is not clear then let me knw

    Tuesday, February 11, 2014 1:05 AM
  • User79986525 posted

    Hi ,

    Try this 

    select 0, DO.disp_office_code as office_code,O.office_name ,G.GROUP_NAME 
    FROM  disp_offices DO, offices O,Groups G 
    WHERE  O.office_code=DO.disp_office_code AND office_code='" & office98 & "' and disp_office_code in
     (select office_code from offices where office_level<>'STOP') AND G.LEDIT=O.LEDIT  order by id"

    Hope this will help you out ..

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 11, 2014 1:38 AM
  • User-2007877324 posted

    First let's remove the unnecessary subqueries from your select statement. You query can be rewritten as below

    select 0, disp_office_code as office_code, office_name
    from disp_offices d
    inner join offices o on o.office_code = d.disp_office_code
    where o.office_level<>'STOP' order by id

    Here is the query with join to groups

    select 0, disp_office_code as office_code, office_name, group_name
    from disp_offices d
    inner join offices o on o.office_code = d.disp_office_code
    inner join Groups g on g.ID = d.ID
    where o.office_level<>'STOP' order by id

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 11, 2014 4:16 AM