Select count(*) from table where (multiple id) in (table)

Question

Is there a way to write

SELECT count(*) from tablename where (multiple_ids_here) in (SELECT id from tablename)

Normally, I would write:

select count(*) from tablename 
where id_1 in (SELECT id from tablename)
OR id_2 in (SELECT id from tablename)
id_3 in (SELECT id from tablename)

which very inefficient if we have multiple values.

Anyone?

EDIT: Question updated. What if I want to select count?

can you elaborate little more what the issue you are facing ? of course yu can write 3 different in statements — starthis, Apr 19 '17 at 12:26
That query will either return 0, or the number of rows in the table... Doesn't make much sense. — jarlh, Apr 19 '17 at 12:34

score 2 · Answer 1 · answered Apr 19 '17 at 12:20

2

Your version with three ins is probably the most efficient way of doing this. If you want a comparison to try, you can use exists:

select . . .
from t t1
where exists (select 1
              from tablename t2
              where t2.id in (t1.id_1, t1.id_2, t1.id_3)
             );

I should also note that storing ids in multiple columns like this is usually a sign of a problem with the data model. You probably want a table with one row per id, rather than one column per id. Such a format would also simplify this type of query.

answered Apr 19 '17 at 12:20

Gordon Linoff

1,242,037
58
646
786

the ids are the same columns for a table but are different items. ie. Person1, SisterofPerson, Brother, etc.. but they are on the same `Person` table – xGeo Apr 19 '17 at 12:22
what if I want to use count? I'll update the question. – xGeo Apr 19 '17 at 12:22
3

What if someone has two sisters? A relationship table makes more sense. – Gordon Linoff Apr 19 '17 at 12:23
there is a relationship table. and having multiple sisters is handled well. (though `Persons` is just an example) – xGeo Apr 19 '17 at 12:26

justinf · Answer 2 · 2017-04-19T12:51:44.593

I am not sure i understand your question could you give an example of the data you are using and the out come.

From what i understand you could use a cte like this .

;WITH Sales_CTE ([counts],CustomerID, SalespersonPersonID,PickedByPersonID)
AS
(
   select count(*),CustomerID,SalespersonPersonID ,PickedByPersonID 
   from [WideWorldImporters].[Sales].[Orders]
   group by CustomerID,SalespersonPersonID,PickedByPersonID
)
SELECT sum([counts])
FROM Sales_CTE
GO

It would give you a result like this . You would jsut have to change the columns around .

score 0 · Accepted Answer · answered Apr 19 '17 at 12:34

For the updated question regarding getting a count(*)... using cross apply() with values() to unpivot your data in a common table expression:

;with cte as (
  select t.Id, v.RelatedId
  from t
    cross apply (values (id_1),(id_2),(id_3)) v(RelatedId)
)

select 
    cte.Id
  , RelationCount = count(*)
from cte
  inner join RelatedTable r
    on cte.RelatedId = r.Id
group by cte.Id

Select count(*) from table where (multiple id) in (table)

3 Answers3