Using Multiple Row Criteria In the Where Clause

Question

I'm trying to formulate a query that will allow me to return all of the rows with the same ID number if one of the rows meets a certain criteria. My current table and desired tables are as follows:

Current Table:

ID  Parameter
1   x
1   x
1   y
1   x
2   x
2   x
2   x
3   y
3   y
3   x
4   x
4   x
4   x

Desired Results:

ID  Parameter
1   x
1   x
1   y
1   x
3   y
3   y
3   x

In this example, my parameter of interest is "y". Since y appears in at least one of the rows for ID's 1 and 3, then all of the row data for ID's 1 and 3 are selected while the rest are filtered out. Is there a way to write this in a where clause or will I need to consider another method?

Thank you!

score 2 · Accepted Answer · answered Sep 18 '12 at 18:17

2

Try this; it will select all records where any IDs have at least one record with Parameter = 'Y':

SELECT [ID], [Parameter]
FROM YourCurrentTable
WHERE [ID] IN (
    SELECT [ID]
    FROM YourCurrentTable
    WHERE [Parameter] = 'Y'
)

answered Sep 18 '12 at 18:17

LittleBobbyTables - Au Revoir

32,008
25
109
114

score 1 · Answer 2 · edited May 23 '17 at 12:27

1

For performance purpose, you should prefer using EXISTS instead of IN. See this thread to understand differences in EXISTS and IN : SQL Server IN vs. EXISTS Performance

select id, parameter
from current_table c1
where exists (
    select 1
    from current_table c2
    where c2.id = c1.id
    parameter = 'y'
);

edited May 23 '17 at 12:27

Community

1
1

answered Sep 18 '12 at 18:24

Quentin Proust

98
5

Sasa Popovic · Answer 3 · 2012-09-18T18:58:08.053

0

Here's one way you could do this

    create table tret (i int, j char)
    insert into tret values 
    (1,   'x'),
    (1,   'x'),
    (1,   'y'),
    (1,   'x'),
    (2,   'x'),
    (2,   'x'),
    (2,   'x'),
    (3,   'y'),
    (3,   'y'),
    (3,   'x'),
    (4,   'x'),
    (4,   'x'),
    (4,   'x')


    select * from tret where i in (select i from tret where j = 'y')

edited Sep 18 '12 at 18:58

answered Sep 18 '12 at 18:21

Sasa Popovic

53
1
6

1

If you're going to give query examples, make sure they are workable examples. Your insert statement doesn't work, and your select statement has references to `i` and `j` not defined above. The intent is pretty clear, but the example doesn't work. – LittleBobbyTables - Au Revoir Sep 18 '12 at 18:26

Using Multiple Row Criteria In the Where Clause

3 Answers3