Customer(CustNo, FirstName, LastName, Address, State, PostCode)
Agent (AgentNo, AgentName)
Account (AccountNo, AgentNo, CustNo, DateOfTrans)
Customer could have been serve by 2 or 3 Agent in every transaction
- List FirstName, LastName of Customer who have been serve by both Agent John and Agent Lily
- List FirstName, LastName of Customer who have been serve by both Agent John but not Agent Lily
my query statement
select A.AgentNo, C.FirstName, C.LastName, Ag.AgentName from Account A left join Agent Ag on A.AgentNo = Ag.AgentNo Left outer join Customer C on A.CustNo = C.CustNo where not Ag.AgentName = 'Lily'
for question 1, how do i list out those customer that have been serve by the 2 agent exclude those who only serve by either 1 only?
for question 2, how do i list out those customer serve by agent john and not those customer who have been serve bu both john and lily?