SQL query base on three relation with restriction

• Customer (CustomerID, FirstName, LastName, Email, PostCode)
• Salesman (SalesmanNo, empName)
• Account (AccountNo, SalesmanNo, CustomerID, AccountType)

How do I achieve this:

Get the name of customer purchase from both Sean and Jean, i believe with the below nested query statement I get the customerID who purchase from both salesman.

result like

     CustomerID 
1.   043
2.   100
3.   203
4.   011
5.   045
6.   008

((select distinct A.CustomerID as CustomerID from account A
Inner Join Salesman S on A.SalesmanNo = S.SalesmanNo
where S.empname = 'Sean')
Intersect
(select distinct A.CustomerID as CustomerID from account A
Inner Join Salesman S on A.SalesmanNo = S.SalesmanNo
where S.empname = 'Jean'))

but when i add

Select C.Firstname, C.LastName from Customer C
Inner Join
((select distinct A.CustomerID as CustomerID from account A
Inner Join Salesman S on A.SalesmanNo = S.SalesmanNo
where S.empname = 'Sean')
Intersect
(select distinct A.CustomerID as CustomerID from account A
Inner Join Salesman S on A.SalesmanNo = S.SalesmanNo
where S.empname = 'Jean')) 
As CID on C.CustomerID = A.CustomerID

I get error ORA-00905: missing keyword 00905. 00000 – “missing keyword” *Cause:
*Action: and the CID is flag so when i placed my cursor the CID (Syntax Error. Partially recognized rules(railroad diagram):

---

Please help

Answer

Despite the fact that you will get similar results for JOIN and IN, it is better to use IN predicate for filtering and JOIN part to enrich the data with more data. Because over the years from IN you’ll expect nothing more than filtering and will not need to try to remember or understand the purpose of any particular JOIN.

Your error is cause by AS before table alias CID, because Oracle does not expect AS before it. The query is:

Select C.Firstname, C.LastName
from Customer C
where CustomerID in (
 select a.CustomerID
 from Account A
 where a.SalesmanNo in (select s.SalesmanNo from Salesman s where S.empname in ('Sean','Jean'))
 group by a.CustomerID
 having count(distinct a.SalesmanNo) = 2
)