CODEWITHSUNDEEP
mysqlsql
as2d3
as2d3

Reputation: 905

Composite primary key, display entries with one same value and one different

This is how my MySQL table looks like : SQL Table

Here, faculty_id and research_area are the primary keys. I want faculties who work in both “AI” and “Big Data”. So Ravi(F1) and Dham(F3) should be printed.

I tried select * from faculty_details where research_area='AI' AND research_area='Big Data'; and select * from faculty_details where research_area='AI' or research_area='Big Data'; but they didn't work.

Upvotes: 1

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

One method uses group by and having:

select fd.faculty_id
from faculty_details fd
where fd.research_area in ('AI', 'Big Data')
group by fd.faculty_id
having count(*) = 2;

If you can have duplicates, then use:

having count(distinct research_area) = 2

Upvotes: 1

Related Questions