CODEWITHSUNDEEP
sqlsqlite
wren
wren

Reputation: 383

How do you select all rows when data occurs in one of the rows?

I have a table with data

    |FormID|Name|
       1      A
       1      B
       2      A
       2      C
       3      B
       3      C

I am trying to query all rows where Name 'A' appears, however i also want to get all rows with the same FormID when the name occurs

For example Select * from table where name = 'A'

    resultset
  |FormID|Name|
     1      A
     2      A
     1      B
     2      C

Right now i am just querying for the FormID values where the name occurs and then doing another query with the FormID number (Select * from table where formID in (1,2) ) but there must be a way to do this in one sql statement

Upvotes: 0

Views: 58

Answers (2)

Shawn
Shawn

Reputation: 52579

Another approach:

SELECT formid, name
FROM forms
WHERE formid IN (SELECT formid FROM forms WHERE name = 'A')
ORDER BY name;

gives

formid      name      
----------  ----------
1           A         
2           A         
1           B         
2           C

Because the subquery in the IN doesn't depend on the current row being looked at, it only has to be evaluated once, making it more potentially more efficient for large tables.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270993

You can use exists:

select t.*
from t
where t.name = 'A' or
      exists (select 1
              from t t2
              where t2.formid = t.formid and t2.name = 'A'
             );

Actually, the first condition is not necessary, so this suffices:

select t.*
from t
where exists (select 1
              from t t2
              where t2.formid = t.formid and t2.name = 'A'
             );

Upvotes: 2

Related Questions