SQL : Filtering data using multiple column

Question

Sorry if this query is already asked, but i searched and can't find anything that resolve my issue.

I have two tables:

Table1:

Act       Marks   
Paint       6   
Paint       7       
Paint       16      
Paint       17  
Swim        6
Swim        2
Sea        448
Sea         17
Drama       6
Drama       7
Drama      16

Table 2:

Marks   Service_cordinator
  6            X3
  7            A2
  16            A3
  17             X1
  1              X2
  2              X3
  448            X4
  234            X5

Query is to fetch all the Acts which has service coordinator X3,A2, and A3. i.e. query should return Paint and Drama

There is no primary key / foregin key relationship in table. Only relation is Marks which is common among both table.

Welcome to Stackoverflow, and thank you for supplying sample data, but please note this is not a free coding service. You are expected to show a query that you have tried. If you then have difficulties with that query then ask for assistance on that query. You need only to join the 2 tables using marks as the common data, then filter that by Service_cordinator — Paul Maxwell
– Paul Maxwell, Commented Sep 23, 2017 at 1:37

Gordon Linoff · Accepted Answer · 2017-09-23 02:01:08Z

1

Use marks to join the tables and then group by and having:

select t1.act
from table1 t1 join
     table2 t2
     on t1.marks = t2.marks
where t2.Service_cordinator in ('X3', 'A2', 'A3')
group by t1.act
having count(distinct Service_cordinator) = 3;

answered Sep 23, 2017 at 2:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL : Filtering data using multiple column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related