CODEWITHSUNDEEP
sqlsql-server
nsivakr
nsivakr

Reputation: 1595

Matching multiple values in the same SQL query

I've a table with the following structure.

Table Name: CustomerStocks

Structure 
Name                     Varchar(25) 
StockSymbol              Varchar(4)

Following is the sample of table contents

Name StockSymbol

Sam    AAPL
Sam    AMZN
Sam    GOOG

Judy   AAPL
Judy   AMZN

Jen    AMZN

Brian  GOOG
Brian  MSFT

The goal is given a customer name, how do I find out the list of other customers who has similar portfolio. In other words, all the equities of original customers must be present.

Thus, if I'm looking for customers who has similar portfolio of Jen, then the result will be Judy and Sam.

If I'm searching for customers who has similar portfolio of Judy, then the result will be Sam.

However, if I'm searching for customers with Brian's portfolio, it can return either no rows or just Brian.

Is this possible? If so ,how do I write a sql query to achieve this?

Any help is truly appreciated on this.

Upvotes: 3

Views: 2649

Answers (5)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16894

Option with NOT EXISTS() and EXCEPT operators

DECLARE @Name Varchar(25) = 'Judy'       
SELECT *
FROM dbo.CustomerStocks s
WHERE NOT EXISTS (
                  SELECT s3.StockSymbol
                  FROM dbo.CustomerStocks s3
                  WHERE s3.Name = @Name
                  EXCEPT
                  SELECT s2.StockSymbol
                  FROM dbo.CustomerStocks s2
                  WHERE s2.Name = s.Name                        
                  ) AND s.Name != @Name

For improving performance use this index

CREATE INDEX ix_StockSymbol_CustomerStocks ON CustomerStocks(StockSymbol) INCLUDE(Name)

Demo on SQLFiddle

Upvotes: 0

Robert Fricke
Robert Fricke

Reputation: 3643

Okay, so how about this:

declare @Name Varchar(25) = 'Judy'
declare @table (StockSymbol varchar(4))

--Get StockSymbols to search for
insert into @table
select cs.StockSymbol 
from CustomerStocks cs 
where cs.Name = @Name
group by cs.StockSymbol


--Match against the people
select cs.Name from CustomerStocks cs
where (select count(*) 
     from CustomerStocks x 
     inner join @table t on x.StockSymbol = t.StockSymbol
     where x.Name = cs.Name
     group by x.StockSymbol) = (select count(*) from @table t)
group by cs.Name

Upvotes: 0

billkw
billkw

Reputation: 3699

Something like this might work, engine-independent:

SELECT target.Name, 'similar_to', others.Name
FROM (
  SELECT Name, COUNT(*) numStocks
  FROM CustomerStocks
  GROUP BY Name
) target
  INNER JOIN (
    SELECT cs1.Name match, cs2.Name otherName, count(*) commonStocks
    FROM CustomerStocks cs1
      INNER JOIN CustomerStocks cs2
        ON cs1.StockSymbol = cs2.StockSymbol
          AND cs1.Name <> cs2.Name
    GROUP BY cs1.Name, cs2.Name
  ) others ON target.Name = others.match
      AND others.commonStocks >= numStocks.numStocks

Upvotes: 0

jimbobmcgee
jimbobmcgee

Reputation: 1721

(one minor adjustment later...)

I've not tested this but something like...

DECLARE @name VARCHAR(24) = 'Judy';

WITH cte_stocks AS (
    SELECT StockSymbol
    FROM CustomerStocks
    WHERE Name = @name
)
SELECT Name
FROM CustomerStocks cs
LEFT JOIN cte_stocks a ON cs.StockSymbol = a.StockSymbol
GROUP BY cs.Name
HAVING COUNT(DISTINCT a.StockSymbol) = (SELECT COUNT(1) FROM cte_stocks)

...might do it

Upvotes: 2

Robert Fricke
Robert Fricke

Reputation: 3643

This should work:

declare @Name varchar(25) = 'Judy'

select * from CustomerStocks cs
where cs.StockSymbol in 
    (select x.StockSymbol 
     from CustomerStocks x 
     where x.Name = @Name)

Or to exclude Judy:

declare @Name varchar(25) = 'Judy'

select * from CustomerStocks cs
where cs.StockSymbol in 
    (select x.StockSymbol 
     from CustomerStocks x 
     where x.Name = @Name
     and x.Name <> cs.Name)

Upvotes: 0

Related Questions