selecting clients with only one product in SQL database

Question

I have a table that has a primaryCustomerID and PositionCode columns. The PriamryCustomerID contains the clients banking portfolio ID and the PositionCode contains the code of the banking products (WSO and PB).

I have to select clients with WSO PositionCode but there must not have PB code. if select clients where PositionCode is WSO it excludes the PB clients but that doesn't mean that the clients do not have PB.

kindly assist. Thanks

Please provide sample data to show your data and clarify your problem. 10 rows of data would be enough. Also, show the expected result. — Walter_Ritzel, Apr 07 '16 at 13:36
If you use sql-server why you tag mysql? ... please remove the improper tags — ScaisEdge, Apr 07 '16 at 13:40

score 0 · Answer 1 · answered Apr 07 '16 at 13:42

This will list all customer with a position code = 'WSO' that do not have also a line with position code = 'PB'. Should work both on sql-server and mySQL.

SELECT a.primaryCustomerID, a.PositionCode
from table a
where a.positionCode = 'WSO'
and not exists (select 1 from table b where b.primaryCustomerID = a.primaryCustomerID and b.positionCode = 'PB')

score 0 · Answer 2 · answered Apr 07 '16 at 13:48

primaryCustomerID - PositionCode 1 PB 1 WSO 2 WSO 3 PB 4 PB 4 WSO

Assuming this is your table and you are expecting the query to return Primary CustomerID - 2

Here is a sample query in mysql(assuming table name to be xyz), not an optimized query but returns result 2,

select * from xyz where positionCode = 'WSO' and PrimaryCustomerID not in ( select PrimaryCustomerID from xyz where positionCode = 'PB' )

score 0 · Answer 3 · edited Sep 13 '21 at 11:27

0

You can use -

SELECT a.primaryCustomerID, a.PositionCode
FROM table a LEFT JOIN table b ON b.primaryCustomerID = a.primaryCustomerID
WHERE  b.positionCode = 'PB' and a.positionCode = 'WSO' and B.primaryCustomerID IS NULL

edited Sep 13 '21 at 11:27

Zaheer Ul Hassan

771
9
24

answered Sep 13 '21 at 10:26

SAHIL GANDHI

1

You should consider adding some explanation as to how/why your proposed code/solution is better than the others already posted (some time ago). – Adrian Mole Sep 13 '21 at 12:32

selecting clients with only one product in SQL database

3 Answers3