CODEWITHSUNDEEP
mysqlsql
Vladimir
Vladimir

Reputation: 836

SQL query to compare rows with each other based on relational data from other tables

First I apologize in advance if my question is too broad. I do not have a lot of experience with SQL and I am struggling with designing a query for a very specific task. In no way I am asking for someone to do all of the work. I just want some guidance on how to build the query and if it is a good solution for the task to be done entirely in SQL at all.

The query I am trying to build needs to list all rows from one table, matched with each other and compare them by checking in another table how many rows have matched. To illustrate I have the following tables:

members (member_id, name)
questions (question_id, title)
answers (answer_id, question_id, title)
members_answers (member_id, question_id, answer_id)
members_acceptable_answers (member_id, question_id, answer_id)

Each member has only one record in members_answers and multiple records in members_acceptable_answers.

What I am trying to show is a list with each members and how many of their acceptable answers have been matched with other members' answers producing a result like this:

member_id | member_id | total_intercepted |
-------------------------------------------
        1 |         2 |                10 |
        2 |         3 |                 6 |
        1 |         3 |                 3 |

I can make the data on the application level but I want to know if there's a proper way for this to be done in the database. I have been experimenting with different queries but none of them are even close to producing the result and they aren't even worth mentioning here.

Again - I just want a guidance on how to build my query and opinion if such a task is even appropriate for SQL only.

Thank you in advance.

Upvotes: 1

Views: 448

Answers (2)

1tiger1
1tiger1

Reputation: 67

This might be a good start... you might need some indexes to pull this off, but here is some starting SQL:

SELECT ma.member_id AS 'Member 1',
       ma2.member_id AS 'Member 2',
       count(maa.answer_id) AS 'Total Matches'
FROM members_answers ma
  JOIN questions q
    ON q.question_id = ma.question_id
  JOIN questions q2 
    ON q.question_id = q2.question_id
  JOIN members_answers ma2
    ON ma2.question_id = q2.question_id
  JOIN members_acceptable_answers maa
    ON  ma2.answer_id = maa.answer_id 
    AND ma.member_id = maa.member_id
GROUP BY 1,2
ORDER BY 1,2

This way.. you are only counting members who answered questions that the potential matching members asked, then you are counting up the total answers that match, including the second member.

Hope this helps...

-Tony

Upvotes: 1

sam
sam

Reputation: 445

I think the good place for you to start learning the sql will be http://www.w3schools.com/sql/default.asp , To bring data from multiple tables you need to join the tables , to determine which join needs to be done check out this article http://sqlbisam.blogspot.com/2013/12/InnerJoin-LeftOuterJoin-RighOuterJoin-and-FullOuterJoin.html and to compare data use except statement

Upvotes: 0

Related Questions