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.

I should note that there aren't going to be just 2 type id's in the query. This is part of a bigger query and the number of type id's passed will be 2 or more. I would have thought there would have been a simpler solution to this problem as mapping tables are common in databases. — Josh, Dec 18 '09 at 21:23
State the full problem with the expected inputs and behavior. If three are in the query and it has to match all three or what? — Cade Roux, Dec 19 '09 at 00:22
How are you passing the IDs to the query? A single comma-separated variable or a table? — Cade Roux, Dec 19 '09 at 00:23

score 5 · Answer 1 · answered Dec 18 '09 at 18:36

5

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.

answered Dec 18 '09 at 18:36

Cade Roux

88,164
40
182
265

What do I do if I need to add another type id to the query? – Josh Dec 18 '09 at 20:06
'Add another type id' Do you need to match all three, or just 2 of the three? – David Oneill Dec 18 '09 at 21:00
It has to match all the type id's passed. So if I want to filter by 2, I will get the object id's that match both type id's. If I want to filter by 3 type id's, I will get the object id's that match all 3 type id's. – Josh Dec 18 '09 at 21:21
You need as many joins as the number of TypeId values you are searching for. – Bill Karwin Dec 18 '09 at 21:38

Bill Karwin · Answer 2 · 2009-12-18T21:40:06.483

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.

edited Dec 18 '09 at 21:40

answered Dec 18 '09 at 18:49

Bill Karwin

538,548
86
673
828

This will fail if it is ever in their with the same TypeId more than once. – David Oneill Dec 18 '09 at 20:59
@David: Right, that's why I noted that I assume uniqueness over TypeId, ObjectId. – Bill Karwin Dec 18 '09 at 21:36
1

This might be preferable to my answer (for maintenance and performance) with the variable lists requirement (review execution plan, of course), but will also fail in the case of 3 matches. I think the requirement of varying input has to be better defined to understand an appropriate final solution. – Cade Roux Dec 19 '09 at 00:21
+1 Yeah, this solution scales better as the number of TypeIds gets larger. See my answer for how to get around the uniqueness issue. – David Oneill Dec 19 '09 at 01:05

David Oneill · Answer 3 · 2009-12-19T01:03:15.207

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.

Yeah, I realized that after posted it. I edited that, and fixed the broken join clause. — David Oneill, Dec 18 '09 at 18:37

priyanka.sarkar · Answer 4 · 2009-12-19T04:09:21.650

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

SQL Select Condition Question

4 Answers4