SQL Select Condition Question

Question

I have a quick question about a select statement condition.

I have the following table with the following items. What I need to get is the object id that matches both type id's.

TypeId  ObjectId
1       10
2       10
1       11

So I need to get both object 10 because it matches type id 1 and 2.

SELECT ObjectId
FROM Table
WHERE TypeId = 1
AND TypeId = 2

Obviously this doesn't work because it won't match both conditions for the same row. How do I perform this query? Also note that I may pass in 2 or more type id's to narrow down the results.

Answer 1

This code is written with Oracle in mind. It should be general enough for other flavors of SQL

select t1.ObjectId from Table t1
join Table t2 on t2.TypeId = 2 and t1.ObjectId = t2.ObjectId
where t1.TypeId = 1;

To add additional TypeIds, you just have to add another join:

select t1.ObjectId from Table t1
join Table t2 on t2.TypeId = 2 and t1.ObjectId = t2.ObjectId
join Table t3 on t3.TypeId = 3 and t1.ObjectId = t3.ObjectId
join Table t4 on t4.TypeId = 4 and t1.ObjectId = t4.ObjectId
where t1.TypeId = 1;

Important note: as you add more joins, performance will suffer a LOT.

In regards to Bill's answer you can change it to the following to get rid of the need to assume uniqueness:

SELECT ObjectId
FROM (SELECT distinct ObjectId, TypeId from Table)
WHERE TypeId IN (1, 2)
GROUP BY ObjectId
HAVING COUNT(*) = 2;

His way of doing it scales better as the number of types gets larger.

Answer 2

Self-join:

SELECT t1.ObjectId 
FROM Table AS t1
INNER JOIN Table AS t2
    ON t1.ObjectId = t2.ObjectId
    AND t1.TypeId = 1 
    AND t2.TypeId = 2

Note sure how you want the behavior to work when passing in values, but that's a start.

Answer 3

I upvoted the answer from @Cade Roux, and that's how I would do it.

But FWIW, here's an alternative solution:

SELECT ObjectId
FROM Table
WHERE TypeId IN (1, 2)
GROUP BY ObjectId
HAVING COUNT(*) = 2;

Assuming uniqueness over TypeId, ObjectId.

---

Re the comment from @Josh that he may need to search for three or more TypeId values:

The solution using JOIN requires a join per value you're searching for. The solution above using GROUP BY may be easier if you find yourself searching for an increasing number of values.

Answer 4

Try this

Sample Input:(Case 1)

declare @t table(Typeid int,ObjectId int)
insert into @t 
    select 1,10 union all select 2,10  union all  
    select 1,11 
select * from @t

Sample Input:(Case 2)

declare @t table(Typeid int,ObjectId int)
insert into @t 
    select 1,10 union all select 2,10  union all 
    select 3,10 union all select 4,10  union all 
    select 5,10 union all select 6,10  union all 
    select 1,11 union all select 2,11  union all 
    select 3,11 union all select 4,11  union all 
    select 5,11 union all select 1,12  union all 
    select 2,12  union all select 3,12 union all 
    select 4,12  union all select 5,12 union all 
    select 6,12  
select * from @t

Sample Input:(Case 3)[Duplicate entries are there]

declare @t table(Typeid int,ObjectId int)
insert into @t 
    select 1,10 union all select 2,10  union all 
    select 1,10 union all select 2,10 union all
    select 3,10 union all select 4,10  union all 
    select 5,10 union all select 6,10  union all 
    select 1,11 union all select 2,11  union all 
    select 3,11 union all select 4,11  union all 
    select 5,11 union all select 1,12  union all 
    select 2,12  union all select 3,12 union all 
    select 4,12  union all select 5,12 union all 
    select 6,12  union all select 3,12

For case 1, the output should be 10

For case 2 & 3, the output should be 10 and 12

Query:

select X.ObjectId from 
(
select 
      T.ObjectId
      ,count(ObjectId) cnt
from(select distinct ObjectId,Typeid from @t)T
where T.Typeid in(select Typeid from @t)
group by T.ObjectId )X
join (select max(Typeid) maxcnt from @t)Y
on X.cnt = Y.maxcnt