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!
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)