Finding a dynamic intersection of a table

Question

I have a query that will return some results like

London      111
London      222
London      333
Manchester  333
Liverpool   111
Liverpool   222
Liverpool   333
Leeds       111
Leeds       222
Leeds       333

My stored procedure takes in a user defined table that is a list of codes like 111 and 222, but it could take in any number of codes depending on user input from a website

Given that 111 and 333 are passed in I want the SP to return London, Liverpool and Leeds (not manchester) because

They all have records with both these codes

Any clues on how I can achieve this?

Thanks!

Hi. What have tried & researched?--see the downvote arrow mouseover text. Also please read & act on [mcve]. — philipxy, Jul 30 '18 at 20:56

Carsten Massmann · Accepted Answer · 2018-07-30T17:26:55.903

Assuming you have two tables like this:

CREATE table towns (town varchar(16), code int);
insert into towns values 
   ('London', 111),('London', 222),
   ('London', 333),('Manchester', 333),
   ('Liverpool', 111),('Liverpool', 222),
   ('Liverpool', 333),('Leeds', 111),
   ('Leeds' ,222),('Leeds',333);

create table codes (ccde int);
insert into codes values (111),(333);

You should try

SELECT town, code FROM towns INNER JOIN codes on ccde=code GROUP BY town
HAVING COUNT(*)=(select count(*) from codes)

Please check out this edited version. My first try was obviously wrong.

You can find a demo here: http://rextester.com/DCWD90908

Edit

Just noticed that my command would give wrong results if there isn't a unique restriction on the columns town, code in towns. In case there can be double records in towns, like 'Manchester', 333 appearing twice, then the following will still deliver the right results:

SELECT town FROM 
  (SELECT DISTINCT * FROM towns) t 
INNER JOIN codes ON ccde=code GROUP BY town
HAVING COUNT(*)=(SELECT DISTINCT COUNT(*) FROM codes)

http://rextester.com/RZJK41394

score 0 · Answer 2 · answered Jul 30 '18 at 16:21

Throw the query results into a temporary table and re-query it like this.

You can do a basic SELECT query.

SELECT name , code 
FROM [table] AS t1 
INNER JOIN [table] AS t2 
 ON t1.name = t2.name
WHERE t1.code = '111' AND t2.code = '333'

This will get inefficient for larger data sets though.

Another method would be to use ROW_NUMBER() and a sub-SELECT.

SELECT DISTINCT name FROM ( 
    SELECT name , ROW_NUMBER () OVER (PARTITION BY name ORDER BY code) AS row_count
    FROM [table] AS t1 
    WHERE t1.code IN ('333' , '111')
    GROUP BY t1.name, t1.code ) AS list
WHERE list.row_count > 1

Finding a dynamic intersection of a table

2 Answers2