-1

I Have a scenario where there are 3 tables, First table have id, name. Second table have id, status, Third table have id, dept_id.

First table is the parent table and have superset of data. Second table doesn’t have all the records while third have all of the records as First table. Table A,Table B, Table C

My query is to retrieve ids based on dept_id. If I pass status then only ids tagged to that dept and status should be displayed else if I don’t pass status or if I pass null then all ids tagged to input dept_id should be displayed.

Here inputs are dept_id and status(not mandatory)

Tables

Expected output: I am passing deptid = 'A' and if I pass status = 'new' then only 1,2 has to be displayed and if I don't pass status then all 1,2,3,4 have to be displayed. I am trying to insert the output into gt table twice by using if status is not null else pass status. I don’t want to write 2 inserts and try to optimize my code here.

If status is not null then
Insert into gt_temp
Select a.id from a,b,c
Where a.id=b.id(+)
And a.id=c.id
And c.dept_id= ‘A’
And b.status= ‘new’;
Else
Insert into gt_temp
Select a.id from a,b,c
Where a.id=b.id(+)
And a.id=c.id
And c.dept_id= ‘A’;

End if;
Sri
  • 1
  • 1

1 Answers1

0

If I understood you correctly, you're passing dept_id and status as parameters. Then you'd outer join b and c to a on id (as it is the only common column).

As dept_id is obligatory, use it "as is"; status can (but doesn't have to) be passed to query so - indicate that in where clause.

Something like this:

insert into gt_temp
  select a.id
  from a left join b on a.id = b.id
         left join c on c.id = a.id
  where c.dept_id = :par_dept_id
    and (b.status = :par_status or :par_status is null)
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Thank you, I would say a and c have same number of records and we don’t need left join in c – Sri Aug 10 '22 at 06:40
  • You're welcome. That's not what you said ("while third have most of the records"). "Most of the records" isn't exactly the "same number of records". At least, that's how I understood it. – Littlefoot Aug 10 '22 at 06:42