CODEWITHSUNDEEP
mysqlsql
Nighthawk
Nighthawk

Reputation: 1

How to exclude certain entries in SQL

I want to show the CompanyName from which the client has purchased products but has never sold to.

The trade table displays all of the trade records client number representing the customer the CompanyName which they traded with and if it was a buy or sell order (TradeType)

I can display from the SQL below all the trades that have been conducted by the client and that were only Buy orders. As seen below.

SELECT CompanyName, ClientName, TradeType
FROM   Client AS C INNER JOIN Trade AS T 
ON     C.ClientId=T.ClientNumber
WHERE  TradeType = 'Buy' AND ClientNumber = 103;

My problem is, there are companies that they also purchased from and they are also showing due to having at least one Buy trade in one of the records.

So how do I exclude all of the companies that the customer has had a selling record with?

It seems simple but for some reason, it is breaking my head trying to learn SQL.

Also, I am currently learning on access/MySQL

For this example the two table headers are Trade (ClientNumber, CompanyName, TradeType), Client (ClientId, ClientName, Adress). Every time a client creates a trade it gets logged in the trade table like a receipt/History.

Client:

| ClientID | ClientName | Address  |
|----------|------------|----------|
| 103      | Sam        | 2 Street |
| 104      | Tom        | 6 street |
| 105      | Michael    | 7 street |

Trade:

| ClientNumber | CompanyName | TradeType |
|--------------|-------------|-----------|
| 103          | Sunsuper   | Buy |
| 103          | Woolworths  | Buy |
| 104          | Coles       | Buy |
| 103          | Sunsuper    | Sell |
| 103          | Audi        | Sell |

In this example using my code, it would display the rows of Sunsuper and Woolworths as they have got Buy trades with client id 103. But I also want to exclude Company names in the output that had any sell Trades with the client.

In this example, Sunsuper had also a Sell trade later so I want the name excluded from the results.

In this example using my code, it would show

Upvotes: 0

Views: 75

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

So how do I exclude all of the companies that the customer has had a selling record with?

One method is aggregation:

SELECT T.CompanyName, C.ClientName
FROM Client C INNER JOIN
     Trade T 
     ON C.ClientId = T.ClientNumber
WHERE T.ClientNumber = 103
GROUP BY T.CompanyName, c.ClientName
HAVING SUM(TradeType = 'Sell') = 0;

Upvotes: 0

Akina
Akina

Reputation: 42844

For all clients:

SELECT T.CompanyName, C.ClientName
FROM Client AS C 
INNER JOIN Trade AS T ON C.ClientId=T.ClientNumber
GROUP BY T.CompanyName, C.ClientName
HAVING SUM(T.TradeType = 'Buy') = COUNT(T.TradeType)

HAVING checks that all rows have 'Buy' trade type.

For definite client add WHERE C.ClientNumber = 103.

Upvotes: 1

Related Questions