Multiple maching values from two tables
-
Hello all. I would love to be able to find a solution to the problem below. There are two tables:activity and person, each containing a different value pair for columns catid1 and catid2. The desired output would be the activity and name columns that satisfy the following condition: a person must have ALL the catid1 and catid2 combinations for a given activity.
If you look at Mike, he satisfies all the requirements for 'running', but not for the others. The output should look like this:
[font="Courier New"]
Mike Running
George Jogging
Marty Cycling
[/font]
Here's some quick code to create the tables with the data:
CREATE TABLE [activity] ([activity] NCHAR(10) NOT NULL,
[catid1] INT NOT NULL,
[catid2] INT NOT NULL)
GO
--
-- [Table] person
--
CREATE TABLE [person] ([name] NCHAR(10) NOT NULL,
[catid1] INT NOT NULL,
[catid2] INT NOT NULL)
GO
INSERT INTO activity (activity, catid1, catid2) VALUES ('running ', 1, 1);
INSERT INTO activity (activity, catid1, catid2) VALUES ('running ', 2, 2);
INSERT INTO activity (activity, catid1, catid2) VALUES ('running ', 3, 3);
INSERT INTO activity (activity, catid1, catid2) VALUES ('jogging ', 10, 10);
INSERT INTO activity (activity, catid1, catid2) VALUES ('jogging ', 20, 20);
INSERT INTO activity (activity, catid1, catid2) VALUES ('swimming ', 5, 6);
INSERT INTO activity (activity, catid1, catid2) VALUES ('swimming ', 6, 7);
INSERT INTO activity (activity, catid1, catid2) VALUES ('swimming ', 8, 9);
INSERT INTO activity (activity, catid1, catid2) VALUES ('swimming ', 7, 8);
INSERT INTO activity (activity, catid1, catid2) VALUES ('cycling ', 10, 10);
INSERT INTO activity (activity, catid1, catid2) VALUES ('cycling ', 1, 5);
INSERT INTO activity (activity, catid1, catid2) VALUES ('cycling ', 1, 1);
INSERT INTO person (name, catid1, catid2) VALUES ('John ', 1, 1);
INSERT INTO person (name, catid1, catid2) VALUES ('John ', 2, 2);
INSERT INTO person (name, catid1, catid2) VALUES ('John ', 3, 3);
INSERT INTO person (name, catid1, catid2) VALUES ('George ', 10, 10);
INSERT INTO person (name, catid1, catid2) VALUES ('George ', 20, 20);
INSERT INTO person (name, catid1, catid2) VALUES ('George ', 1, 3);c
INSERT INTO person (name, catid1, catid2) VALUES ('Jimmy ', 1, 1);
INSERT INTO person (name, catid1, catid2) VALUES ('Jimmy ', 2, 2);
INSERT INTO person (name, catid1, catid2) VALUES ('Mike ', 1, 1);
INSERT INTO person (name, catid1, catid2) VALUES ('Mike ', 2, 2);4
INSERT INTO person (name, catid1, catid2) VALUES ('Mike ', 3, 3);
INSERT INTO person (name, catid1, catid2) VALUES ('Mike ', 45, 45);
INSERT INTO person (name, catid1, catid2) VALUES ('Mike ', 34, 34);
INSERT INTO person (name, catid1, catid2) VALUES ('Billy ', 10, 10);
INSERT INTO person (name, catid1, catid2) VALUES ('Joey ', 20, 20);
INSERT INTO person (name, catid1, catid2) VALUES ('Marty ', 1, 1);
INSERT INTO person (name, catid1, catid2) VALUES ('Marty ', 10, 10);
INSERT INTO person (name, catid1, catid2) VALUES ('Marty ', 1, 5);
INSERT INTO person (name, catid1, catid2) VALUES ('Marty ', 4, 87);
Any ideas? Many thanks in advance!
-
May something like the following although this will also produce John running:
SELECT *
FROM
(
SELECT P1.[name], A1.activity, COUNT(*) AS CatCount
FROM person P1
JOIN activity A1
ON P1.catid1 = A1.catid1
AND P1.catid2 = A1.catid2
GROUP BY P1.[name], A1.activity
) D1
JOIN
(
SELECT A2.activity, COUNT(*) AS CatCount
FROM activity A2
GROUP BY A2.activity
) D2
ON D1.activity = D2.activity
AND D1.CatCount = D2.CatCount
-
SELECT p.name,a.activity
FROM activity a
INNER JOIN person p ON p.catid1=a.catid1 AND p.catid2=a.catid2
GROUP BY a.activity,p.name
HAVING COUNT(*)=(SELECT COUNT(*) FROM activity a2 WHERE a2.activity=a.activity)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thank you so much!
Mark - the query works great! You are a gentleman and a scholar. 😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply