0

So I am given the following table: 

ResInt (pid, topic) - Person with pid has topic as a research interest

And I need to accomplish the following: 

1. Write a SQL query to find all person-person-interest triplets (pid1, 
pid2, topic) such that person pid1 has topic as a research interest,
but person pid2 doesn’t. Call this the PPT view.

2. Given the earlier view PPT and the original table ResInt,
write the full SQL query to find all pairs of people with the exact
same set ofresearch interests. Remove mirror pairs and self-pairs.

A person can have multiple interests so there may be multiple tuples of the same PID with different topics in the table.

My attempt looks something like this:

CREATE VIEW PPT AS
SELECT T1.pid AS FirstPID, SELECT T2.pid as SecondPID, T1.topic
FROM ResInt AS T1, ResInt AS T2
WHERE T1.pid != T2.pid AND T1.topic != T2.topic

But this would only give me different combinations of tuples in which two different people don't have the same interest listed. I think this would also add way too many tuples that don't belong, as each combination of differing topics would be added, rather than the people which don't have some interest in common.

sinalmighty
  • 1
  • Could you please provide us with an [SQLfiddle](http://sqlfiddle.com/) or at least some sample data as was done in this post: http://stackoverflow.com/questions/35442173/how-to-display-products-under-category-in-sql-in-a-table?answertab=votes#tab-top (just an example of how to provide sample data). – Ralph Feb 22 '16 at 18:58
  • 1
    This looks like homework. We are not here to do your homework for you. – Sean Lange Feb 22 '16 at 19:33
  • 1
    "I think this would..."? Why don't you test it and see? And if it doesn't give the results you want, then edit your question and add your current results and your desired results. Otherwise, we're just guessing too, and with less information to go by than you have. – Tab Alleman Feb 22 '16 at 20:16

0 Answers0