query formation

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.

Answer 1

User364480375 posted

ques is not clear then let me knw

Answer 2

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 ..

Answer 3

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