Combining Complex Queries, Selecting from 4 Tables

Question

I've been trying to combine these queries with no success

Simply selecing fol.r_folder_path from dm_folder fol doesn't work and I am unsure why...

SELECT distinct(rec.r_object_id) as record_obj_id, rec.accession_no, rec.pier_content_type, rec.archive_status, rel.relation_name, t.object_name as template_name, t.exempt_from_review, fol.r_folder_path
FROM dm_relation rel, pier_record rec, pier_template t, dm_folder fol
WHERE rel.parent_id = rec.r_object_id
AND rel.child_id = t.r_object_id
AND rec.accession_no in ('6929860','6929838','6929866','6929825','6929830','6929746','6929688','6929839','6929872','6929816','6929770','6929731','6929715','6929821','6929726','6929815','6929656','6929631','6929621','6929737','6929824','6929627','6929639','6929607','6929571','6929736','6929743','6929722','6929677')

SELECT temp.r_object_id, temp.object_name, temp.title, temp.owner_name, temp.acl_name, fol.r_folder_path 
FROM pier_template temp, dm_folder fol 
WHERE temp.i_folder_id = fol.r_object_id 
AND r_folder_path is not nullstring enable(ROW_BASED)

I am hoping to return the folder path but I get an error 'ORA-00942: table or view does not exist'

Edit: It appears the JOIN clause doesn't exist in DQL, I guess some stuff doesn't cross over... I think then I need to find a shared id in this dm_relation table I can use, perhaps.

Edit2: I think it's working!

 SELECT DISTINCT
 (rec.r_object_id) as record_obj_id,
  rec.accession_no,
  rec.pier_content_type,
  rec.archive_status,
  rel.relation_name,
  t.object_name as template_name,
  t.exempt_from_review,
  fol.r_folder_path
FROM
  dm_relation rel, pier_record rec, pier_template t, dm_folder fol
WHERE
  rel.parent_id = rec.r_object_id AND
  rel.child_id = t.r_object_id AND
  t.i_folder_id = fol.r_object_id AND
  fol.r_folder_path is not null AND
  rec.accession_no in ('6929860','6929838','6929866','6929825','6929830','6929746','6929688','6929839','6929872','6929816','6929770','6929731','6929715','6929821','6929726','6929815','6929656','6929631','6929621','6929737','6929824','6929627','6929639','6929607','6929571','6929736','6929743','6929722','6929677') enable(ROW_BASED)

`distinct` is not a function. It's a part of `select distinct` and applies to the whole selected rows. Remove those extra parentheses to make the code clearer, i.e. simply do `SELECT distinct rec.r_object_id as record_obj_id, rec.accession_no, ...`. — jarlh, Jun 25 '19 at 09:31
Tip of today: Always use modern, explicit `JOIN` syntax. Easier to write (**without errors**), easier to read (and maintain), and easier to convert to outer join if needed. — jarlh, Jun 25 '19 at 09:32
The reason why you think you need DISTINCT in the first query is because you have **four tables** in the FROM clause and join criteria for only **three** of them in the WHERE clause. This is the sort of bloomer that switching to explicit JOIN syntax would prevent. Also your second query has that weird `nullstring enable(ROW_BASED)` at the end of it. Not sure whether that's your actual code or just an artefact of your cut'n'paste into the textbox. Either way you need to fix your code before we can start to improve it for you. — APC, Jun 25 '19 at 10:13

score 2 · Answer 1 · answered Jun 25 '19 at 10:35

If I understand you correctly, you are getting too many results from the first query and the second query is an attempt to filter the results more fully. That makes sense, because as APC already said you are lacking a JOIN condition on DM_FOLDER.

It seems easy to add the conditions from the second query to the first:

create table dm_relation(relation_name,parent_id,child_id) as
select 'relation_name', 1,2 from dual;
create table pier_record(r_object_id,pier_content_type,archive_status,accession_no) as
select  1,'pier_content_type','archive_status',6929860 from dual;
create table pier_template(title, owner_name, acl_name,object_name,exempt_from_review,r_object_id,i_folder_id) as
select  'title','owner_name','acl_name','object_name','exempt_from_review',2,3 from dual;
create table dm_folder(r_folder_path,r_object_id) as
select 'r_folder_path',3 from dual
union all
select null,4 from dual
union all
select 'r_folder_path2', 5 from dual;

SELECT rec.r_object_id as record_obj_id, 
rec.accession_no, rec.pier_content_type, rec.archive_status, rel.relation_name, 
t.object_name as template_name, t.exempt_from_review, fol.r_folder_path
FROM dm_relation rel, pier_record rec, pier_template t, dm_folder fol
WHERE rel.parent_id = rec.r_object_id
AND rel.child_id = t.r_object_id
AND rec.accession_no in ('6929860','6929838','6929866','6929825','6929830','6929746','6929688','6929839','6929872','6929816','6929770','6929731','6929715','6929821','6929726','6929815','6929656','6929631','6929621','6929737','6929824','6929627','6929639','6929607','6929571','6929736','6929743','6929722','6929677')
and t.i_folder_id = fol.r_object_id 
AND fol.r_folder_path is not null;

RECORD_OBJ_ID ACCESSION_NO PIER_CONTENT_TYPE ARCHIVE_STATUS RELATION_NAME TEMPLATE_NA EXEMPT_FROM_REVIEW R_FOLDER_PATH 
------------- ------------ ----------------- -------------- ------------- ----------- ------------------ --------------
            1      6929860 pier_content_type archive_status relation_name object_name exempt_from_review r_folder_path

Combining Complex Queries, Selecting from 4 Tables

1 Answers1