Matching multiple values in the same SQL query

Question

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.

What version of Sql Server -- 2008 or higher by chance?

sgeddes
– sgeddes

2013-03-15 23:01:23 +00:00
Commented Mar 15, 2013 at 23:01 — sgeddes
– sgeddes, Commented Mar 15, 2013 at 23:01
sgeddes - Using Sql Server 2008 R2.

nsivakr
– nsivakr

2013-03-15 23:02:26 +00:00
Commented Mar 15, 2013 at 23:02 — nsivakr
– nsivakr, Commented Mar 15, 2013 at 23:02

jimbobmcgee · Accepted Answer · 2013-03-15 23:06:22Z

2

(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

edited Mar 15, 2013 at 23:06

answered Mar 15, 2013 at 22:50

jimbobmcgee

1,73113 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

xQbert Over a year ago

Author may want to include all those whose set is at least the same as . if so... having should be >= that way its at least all the same stocks. similar but must contain all doesn't mean the exact same in my mind.. but let the OP decide

jimbobmcgee Over a year ago

@xQbert - Not sure it makes a difference; I'm trying to think of an example that would demonstrate the difference between = and >= in this case. In the OP's limited setup, >= and = return the same. Can you provide an example?

xQbert Over a year ago

Customer has Stocks A, B, C User 2 has Stocks A, B, C, D wouldn't this as written exclude user 2?

jimbobmcgee Over a year ago

@xQbert - Isn't that the same as (in this case) Sam and Judy? Judy (Customer / @name) has A, B (AAPL, AMZN); Sam (User 2) has A, B, C (AAPL, AMZN, GOOG); Sam and Judy appear in result for both = and >=

nsivakr Over a year ago

jimbobmcgee , According to your select statement, HAVING COUNT(DISTINCT a.StockSymbol) = (SELECT COUNT(1) FROM cte_stocks). Can you throw some light on how this query works?

|

Robert Fricke · Accepted Answer · 2013-03-15 22:44:25Z

0

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)

answered Mar 15, 2013 at 22:44

Robert Fricke

3,6531 gold badge23 silver badges35 bronze badges

3 Comments

nsivakr Over a year ago

The rule is the entire set of stocks held by Judy must be matched by other customers and just not one. Thus, if Judy has AAPL, AMZN and GOOG , then the resultant row should be only the list of customers who has all these three stocks and just not one of them.

Richard Marskell - Drackir Over a year ago

This won't work as the asker wanted. The IN clause will match any of the associated values, but not necessarily all of them.

nsivakr Over a year ago

Thanks Robert for your effort. However, this doesn't help.

billkw · Accepted Answer · 2013-03-15 23:21:07Z

0

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

answered Mar 15, 2013 at 23:21

billkw

3,7873 gold badges30 silver badges34 bronze badges

Comments

Robert Fricke · Accepted Answer · 2013-03-16 00:31:20Z

0

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

edited Mar 16, 2013 at 0:31

answered Mar 15, 2013 at 22:56

Robert Fricke

3,6531 gold badge23 silver badges35 bronze badges

Comments

Oleksandr Fedorenko · Accepted Answer · 2013-03-16 09:14:26Z

0

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

edited Mar 16, 2013 at 9:14

answered Mar 16, 2013 at 8:58

Oleksandr Fedorenko

17k6 gold badges43 silver badges51 bronze badges

Collectives™ on Stack Overflow

Matching multiple values in the same SQL query

5 Answers 5

9 Comments

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

9 Comments

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related