How do i solve this with query

Transact-SQL
1

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

  1. List FirstName, LastName of Customer who have been serve by both Agent John and Agent Lily
  2. 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?

2

Welcome to the forum. this looks awfully like home work? What have you attempted to do so far?

3

I don't know if you've studied the HAVING clause or not (or derived tables, which most people call subqueries, although technically I don't think it's a subquery).

A lot of people don't use HAVING, and don't understand it, but it's especially useful for these types of queries. It's easier to show by example than to explain.

--#1
SELECT C.FirstName, C.LastName
FROM Customer C
INNER JOIN (
    SELECT Ac.CustNo
    FROM Account Ac
    INNER JOIN Agent Ag ON Ag.AgentNo = Ac.AgentNo
    WHERE Ag.AgentName IN ('John', 'Lily')
    GROUP BY Ac.CustNo
    HAVING 
        /* 'John' must be present */
        MAX(CASE WHEN Ag.AgentName = 'John' THEN 1 ELSE 0 END) = 1 AND
        /* 'Lily' must be present */
        MAX(CASE WHEN Ag.AgentName = 'Lily' THEN 1 ELSE 0 END) = 1 --<<--<<--
) AS Ac ON C.CustNo = Ac.CustNo 

--#2
SELECT C.FirstName, C.LastName
FROM Customer C
INNER JOIN (
    SELECT Ac.CustNo
    FROM Account Ac
    INNER JOIN Agent Ag ON Ag.AgentNo = Ac.AgentNo
    WHERE Ag.AgentName IN ('John', 'Lily')
    GROUP BY Ac.CustNo
    HAVING 
        /* 'John' must be present */
        MAX(CASE WHEN Ag.AgentName = 'John' THEN 1 ELSE 0 END) = 1 AND
        /* 'Lily' must NOT be present */
        MAX(CASE WHEN Ag.AgentName = 'Lily' THEN 1 ELSE 0 END) = 0 --<<--<<--
) AS Ac ON C.CustNo = Ac.CustNo
4

Here are alternatives that don't use HAVING.

--#1
SELECT C.FirstName, C.LastName
FROM Customer C
INNER JOIN (
    SELECT DISTINCT Ac.CustNo
    FROM Account Ac
    INNER JOIN Agent Ag ON Ag.CustNo = Ac.CustNo
    WHERE Ag.AgentName = 'John'
) AS John ON C.CustNo = John.CustNo
INNER JOIN (
    SELECT DISTINCT Ac.CustNo
    FROM Account Ac
    INNER JOIN Agent Ag ON Ag.AgentNo = Ac.AgentNo
    WHERE Ag.AgentName = 'Lily'
) AS Lily ON John.CustNo = Lily.Custno

--#2
SELECT C.FirstName, C.LastName
FROM Customer C
INNER JOIN (
    SELECT DISTINCT Ac.CustNo
    FROM Account Ac
    INNER JOIN Agent Ag ON Ag.CustNo = Ac.CustNo
    WHERE Ag.AgentName = 'John'
) AS John ON C.CustNo = John.CustNo
LEFT OUTER JOIN (
    SELECT DISTINCT Ac.CustNo
    FROM Account Ac
    INNER JOIN Agent Ag ON Ag.AgentNo = Ac.AgentNo
    WHERE Ag.AgentName = 'Lily'
) AS Lily ON John.CustNo = Lily.CustNo
WHERE Lily.CustNo IS NULL
1 Like
5

Thank you Scott, butAgent relation does not have attribute CustNo, so is this suppose to be AgentNo?
INNER JOIN Agent Ag ON Ag.CustNo = Ac.CustNo

6

Yes, sorry, that join should be on AgentNo.
Edit: I've correct the JOIN columns in my posts above.

7

I would like to ask if let said customer name Jeffrey (under Customer FirstName Attribute) made 3 account (under Account) with 3 different agent (under AgentName)

how and what method do i opt jeffrey out in Q2 since he have made the account with Agent Lily?
and in Q1 what to do to list those cutomer who have deal with John and Lily?

Can i do something like programimng as in
For Customer who have deal both Agent John and Lily, display them
for customer who have deal with Agent John and not Lily, display them?