I am writing stored procedure for my report to get the results from the 5 tables.


My SQL is :

SELECT rgn.Region,biz.Rbu,usr.User,biz.BizGroup,per.Role, eff.ApproverStarDate,eff.ApproverEndDate
FROM Region_08 rgn,
User_07 usr,
Person_26 per,
Effec_27 eff,
Biz_09 biz
where rgn.jde_id =eff.jde_id
and usr.per_id = per.per_id
and eff.biz_id = biz.biz_id
and per.per_role_id = eff.per_role_id
and per.role IN ('Requestor' ,'BuApprover')
Order by rgn.Region,biz.Rbu,usr.User

Results:
NorthAmerica AB John 239 Requestor 01/03/2007 null
NorthAmerica MEAT Peter 239 BuApprover 06/03/2007 null
NorthAmrica MEAT Peter 239 BuApprover 04/03/2007 null
LatinAmerica GOAT chip 123 Requestor 03/02/2007 4/04/2007
LatinAmerica GOAT chip 145 BuApprover 03/05/2007 null
LatinAmerica GOAT chip 146 BuApprover 03/08/2007 null
NorthAmerica PORK Peter 239 Requestor 04/03/2007 null
NorthAmerica PORK Peter 146 BuApprover 05/06/2007 null
NorthAmerica MA John 239 Requestor 01/03/2007 null
NorthAmerica MA John 239 Requestor 01/05/2007 null
NorthAmerica MA John 239 Requestor 01/07/2007 null

But above results are not correct for me.Because my report requires List of Users that are both in Requestors and BuApprovers.

Expected Results are :

LatinAmerica GOAT chip 123 Requestor 03/02/2007 4/04/2007
LatinAmerica GOAT chip 145 BuApprover 03/05/2007 null
LatinAmerica GOAT chip 146 BuApprover 03/08/2007 null
NorthAmerica PORK Peter 239 Requestor 04/03/2007 null
NorthAmerica PORK Peter 146 BuApprover 05/06/2007 null

Explanation:
I don't want to see any user having only one Requestor or One BuApprover. If any user having Requestor and also he should have atleast one BuApprover then show me all the records to that perticular user.

Please let me know if you have any questions. But your help much appreciated.

Thanks
Remata.

So then you should add a predicate in the WHERE clause like:

This should also work:
In this solution, we first retain only those persons that are both 'Requestor' and 'BuApprover' by using the INTERSECT operator.

I don't know which solution will be the most efficient, Stolze's or this. If you give both solutions a try, I'd be very much interested in the result.

First of all,
Thanks for your great ideas. Apprciated.
Stolze,
I tried with your way but did not worked for me. The results are nothing. If I comment on Requestor and leave BUApprover gets all BuApprover related and Vice varsa.

Wim,
your query gives no results. The sub query gives results if I take out the other two selects. But we need those two to join with other tables.
E.g SELECT per.per_id,
FROM Person_26
WHERE per.role = 'Requestor'
INTERSECT
(SELECT per.per_id,
FROM Person_26
WHERE per.role = 'BuApprover')


Is there any modifications required for your query ? How about Exists statement?

Please update if any

Thanks
Remata

If you can provide me with the DDL's (only the strict necessary columns) of the used tables and some INSERT statements that populates them, I'm willing to look further into this.

You seem to have a strange schema for your data. Maybe you could explain the schema and tell us how one "user" is associated with "person" and if one "person" can be Requestor and BuApprover together or not. I.e. some semantics would be helpful.

p.s: Other than that, it is usually just "formulating exactly what you want the query to return in plan text, the apply SQL syntax." Works pretty much all the time in a very straight-forward manner.

I agree. The design was not at all good. It's strange for me.

Sorry guys for not giving a clear picture to you both. And also Person or User both are same. Let's treat it as User.
User can have multiple roles.
Firstly User will create by himself or others with his username and userid and e.t.c and then Roles assigned to him.Roles for ResponsibleBusinessUnits(RBU).
So, We want all the users having same RBU with both Requestor and BuApprover roles should display on the report.
I have attached model and description.Thanks for spending time for me. I do appreciate your valuable time and energy.


Thanks
Remata.