CODEWITHSUNDEEP
sqlsql-serverdatabaseresultset
Federico Giust
Federico Giust

Reputation: 1793

Getting single records from multiple rows by id in one column

I'm trying to get some records from a query in SQL Server 2008.

The way the table is store is like this

P_Id        Activity_Id
-----------------------
1234        53
1234        510
4567        65
4567        510
3456        53
3456        540
5678        53
5678        510

Now what I need to get is which P_Id booked Activity 53 & 510 or 65 & 510

If I do something like this, it gives me no records

SELECT  P_Id FROM ActivitiesPerPerson WHERE Activity_Id = 53 AND Activity_Id = 510

If I do this

SELECT  P_Id FROM ActivitiesPerPerson WHERE Activity_Id IN (53, 65, 510)

It gives me some results but it give me people that booked activity 53 only together with the rest.

I need to get the person id that booked 53 & 510 together and the person id that booked 65 & 510 together. All on the same result if possible.

I've got an sql fiddle with this http://sqlfiddle.com/#!3/b4c11/2

Thanks in advance

Upvotes: 2

Views: 87

Answers (4)

StuartLC
StuartLC

Reputation: 107247

Interpreting your question as 'find all persons who have done ALL activities in a given set', you can do like so:

SELECT P_Id, COUNT(*) 
  FROM ActivitiesPerPerson 
  WHERE Activity_Id IN (50, 510, 800)
  GROUP BY P_Id
  HAVING COUNT(*) = 3;

(Adjusted for your fiddle data, and this assumes that ActivitiesPerPerson is unique per (PersonId, ActivityID) pair - i.e. that the same person can't do the same activity more than once. You'll also need to adjust the count = xevery time you change the number of criteria.

If you need to exclude the possibility of duplicates of the same (person, activity) combination giving false positives, distinct them like so:

SELECT P_Id, COUNT(*)
FROM
(
   SELECT DISTINCT P_Id, Activity_ID
   FROM ActivitiesPerPerson 
   WHERE Activity_Id IN (50, 510, 800)
 ) X
GROUP BY P_Id
HAVING COUNT(*) = 3;

The Activities criteria set can be expressed as a table variable or table valued parameter - this helps with maintainablity of this approach (notably the count = x variation). Another Fiddle

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

You would scan the table once and group by P_Id. Having done so, you need an aggregate to tell you wether there is an Activity_Id x for that P_Id. You can simply sum with a case clause for that. Then take only those P_Id matching your criteria:

SELECT P_Id
FROM ActivitiesPerPerson 
WHERE Activity_Id IN (53, 65, 510)
GROUP BY p_Id
HAVING
(
  SUM( CASE WHEN Activity_Id = 53 THEN 1 ELSE 0 END ) > 0
  AND
  SUM( CASE WHEN Activity_Id = 510 THEN 1 ELSE 0 END ) > 0
)
OR
(
  SUM( CASE WHEN Activity_Id = 65 THEN 1 ELSE 0 END ) > 0
  AND
  SUM( CASE WHEN Activity_Id = 510 THEN 1 ELSE 0 END ) > 0
);

See http://sqlfiddle.com/#!3/b4c11/38.

Upvotes: 1

Declan_K
Declan_K

Reputation: 6826

If you always need the intersection of just 2 activities, you could do something like the following. I would wrap it in a Table Function that takes the 2 activitiy IDs as input variables.

Declare @Act1 Int
Declare @Act2 Int

Set @Act1 = 50
Set @Act2 = 510

SELECT A1.P_Id 
FROM   
       ActivitiesPerPerson A1
INNER JOIN
       ActivitiesPerPerson A2
ON     A1.P_Id = A2.P_Id
WHERE  A1.Activity_Id = @Act1
AND    A2.Activity_Id = @Act2

SQL Fiddle here

Upvotes: 0

Bobby
Bobby

Reputation: 2928

This is a bit horrible but works. Numbers taken from your fiddle.

SELECT *
FROM
   (SELECT P_Id
   FROM ActivitiesPerPerson 
   WHERE Activity_Id = 510) AS x INNER JOIN
   (SELECT P_Id
   FROM ActivitiesPerPerson 
   WHERE Activity_Id = 50) AS y ON x.P_Id = y.P_Id INNER JOIN
   (SELECT P_Id
   FROM ActivitiesPerPerson 
   WHERE Activity_Id = 800) AS z ON x.P_Id = z.P_Id

Upvotes: 1

Related Questions