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)

Answer 1

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