select data having same id

Question

id   idtest  result
1      1        2
1      2        1
1      3        2
2      1        2
2      2        1
2      3        1
3      1        1
3      2        2
3      3        1

Would like to get all the rows with the same IDs that matches the condition. For example: get all the rows with the same id where (idTest=2 and result=1) and (idTest=3 and result=2)

result:

id   idtest  result
1      1        2
1      2        1
1      3        2

What would be the query??? Thanks!

Answer 1

Do you mean this?

SELECT * FROM table WHERE id = 1 and (result = 1 OR result = 2)

How about his:

SELECT * 
FROM table WHERE (idTest = 2 OR idTest = 3) AND (result=1 OR result=2)

Answer 2

ID test res

1 1 2

1 2 1

1 3 2

2 1 1

2 2 2

2 3 2

3 1 1

3 2 2

3 3 1

Sorry. This would be my table. and Would like to get all the rows with the same IDs that matches the condition. For example: get all the rows with the same id where (test=2 and res=1) and (test=3 and res=2)

Result:

ID test res

1 1 2

1 2 1

1 3 2

What would be the query in order to get the three rows ?? Thanks!

Answer 3

You seem to want all rows for id's that have rows with that particular combination. What about:

with ids as (
   select id
   from mytable
   where (idTest=2 and result=1) or (idTest=3 and result=2)
   group by id
   having count(id) = 2
)
select mytable.* from mytable
inner join ids
  on ids.id = mytable.id

This gets a list of id's where both conditions apply, and then gets all rows for those id's.

SqlFiddle

Answer 4

You can use EXISTS:

SELECT id, idTest, result
FROM dbo.TableName t
WHERE EXISTS
(
   SELECT 1 FROM dbo.TableName t2
   WHERE t.id = t2.id 
     AND(
        ( t2.idTest=2 AND t2.result=1 )
       OR
        ( t2.idTest=3 AND t2.result=2 )
     )
)

Demo

Update: result is different:

id  idTest  result
1     1     2
1     2     1  <-- satisfies your condition
1     3     2  <-- satisfies your condition
2     1     2
2     2     1  <-- satisfies your condition
2     3     1

So either my understanding was incorrect or your expected result. I have also all ID=2 because the second id-2 row matches the condition.

Answer 5

SELECT  * 
FROM    table t1
WHERE   EXISTS (SELECT 1 FROM table WHERE id = t1.id AND idtest = 2 AND result = 1)
        AND EXISTS (SELECT 1 FROM table WHERE id = t1.id AND idtest = 3 AND result = 2)

Just keep adding more AND EXISTS if you need more

Or you can use IN if it makes more sense to you

SELECT  * 
FROM    table
WHERE   id IN (SELECT id from table where idtest = 2 and result = 1)
        AND id IN (SELECT id from table where idtest = 3 and result = 2)

Answer 6

do you need a method that treats the condition(s) in a generic way, like if they're values in another table? Or do you only need a way to pull results of two independent conditions?

If the latter, then this should work:

SELECT
  id
FROM
  (SELECT   id 
   FROM tbl 
   WHERE    idTest=2 AND RESULT=1) cond1 INNER JOIN
  (SELECT   id 
   FROM tbl 
   WHERE    idTest=3 AND RESULT=2)  cond2   ON
cond1.id = cond2.id

otherwise, if your conditions are generic and stored in a table, you'd need something like:

SELECT
id
FROM
(SELECT id
FROM    tbl FULL OUTER JOIN conditions c
WHERE   c.isUseMe = 1 AND c.SEQ = 1 AND idTest=c.idTestVal AND result=c.resultVal) cond1 INNER JOIN
(SELECT id
FROM    tbl FULL OUTER JOIN conditions c
WHERE   c.isUseMe = 1 AND c.SEQ = 2 AND idTest=c.idTestVal AND result=c.resultVal)  cond2   ON
cond1.id = cond2.id

if the 4 values are passed in as parameters, you'd need something like:

SELECT
  id
FROM
  (SELECT   id
   FROM tbl 
   WHERE     idTest=@idTestVal1 AND result=@resultVal1) cond1 INNER JOIN
  (SELECT   id
   FROM tbl 
   WHERE     idTest=@idTestVal2 AND result=@resultVal2)  cond2  ON
  cond1.id = cond2.id