CODEWITHSUNDEEP
sql
user1693131
user1693131

Reputation: 31

SQL query to retrieve a group records

I'm not sure how to describe this, but I'm trying to retrieve all the records that have a common index and have a specific value in a separate field...

Table: Response

responseID     objective   
===============================
AAA            Posted          
AAA            Aligned      
AAB            Aligned
AAB            Null
AAC            Posted
AAC            Null

Based on the value "Posted" in the objective field, the query would return the following from the table response:

responseID     objective   
===============================
AAA            Posted          
AAA            Aligned      
AAC            Posted
AAC            Null

Any help is greatly appreciated. Especially help with the vocabulary terms as I'm just beginning with queries.

Upvotes: 0

Views: 50

Answers (4)

Codeman
Codeman

Reputation: 12375

You're looking to get rows for any keys in responseId for which there is a value 'Posted' in the column objective. The SQL term for that is "exists" - do any rows exist in this query?

This should work just fine:

DECLARE VARCHAR(50) @objective = 'Posted';

select data1.*
from data data1
where exists 
(
  select 1
  from data data2
  where data2.responseid = data1.responseid 
    and data2.objective = @objective
);

Here's a SQLFiddle showing that it works in MS SQL Server 2012

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270401

I think you can do what you want with exists -- that is, get all responses where one of the objectives is Posted:

select r.*
from response r
where exists (select 1
              from response r2
              where r2.responseid = r.responseid and r2.objective = 'Posted'
             );

Upvotes: 0

Brian DeMilia
Brian DeMilia

Reputation: 13248

select r.responseid, r.objective
  from response r
  join (select responseid from response where objective = 'Posted') v
    on r.responseid = v.responseid

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93734

Use exists operator to get the Response which has objective 'Posted'.

SELECT responseID, objective 
FROM Response a
WHERE exists (select 1 from Response b where a.responseid = b.responseid
and b.objective = 'Posted')

Upvotes: 0

Related Questions