I have an online application I am building with PHP and MySQL where users register and are paired off with one another, after which they answer a series of questions on a scale of 1 - 5. Only two users can be paired with one another and only their answers are to be compared. Those answers are stored in a tabled called answers. I have attached an image of the table structure, but here is below just in case; it is pretty simple:
- answer_ID (primary key)
- question_ID (foreign key that links to questions table)
- user_ID (foreign key links to user table that tells what user answered the question)
- answer (this is the answer value, always 1 - 5)
- favorite (tag to highlight the answer for other purposes in the app)
- discuss (tag to highlight the answer for other purposes in the app)
After users have answered questions they can go to a page to see how their answer's matched the user's they are paired with. I have this page working as I want with all the queries except for the last scenario below. My main desire is to have SQL do the lifting on matching results instead of using PHP programming to compare arrays.
My goal: To have a query that shows all the questions (linked to questions table using foreign key in answers table: question_ID) where User-1 answered 1 and User-2 answered 4 or 5 when comparing answers to a specific question. There will be other users answers in this table and I only want the query to consider User-1 and User-2.
I will always be comparing results where User-1 answered the question with a value of 1 and User-2 answered with a value of 4 or 5. Questions can only be answered once and it is not a requirement that both users answer all questions in the application before the comparison is made; the query should just poll the answers in the table and match the question_ID column to find any results that should be returned.
Here are some scenarios and expected outcomes:
Q1) IF User-1 Answer Value = 1 and User-2 Answer Value = 5: Show question.
Q2) IF User-1 Answer Value = 1 and User-2 Answer Value = 4: Show question.
Q3) IF User-1 Answer Value = 1 and User-2 Answer Value = 2: Don’t show question.
Q4) IF User-1 Answer Value = 3 and User-2 Answer Value = 3: Don’t show question.
Q5) IF User-1 Answer Value = 3 and User-2 Answer Value = 4 or 5: Don’t show question.
Q6) IF User-1 Answered Value = 5 and User-2 Answered Value = 4: Don’t show question.
Q2) IF User -1 Answer Value = 1 and User-5 Answer Value = 4: Don't show question.
Q7) IF User-1 Answer Value = 1 and User-2 has not answered: Don't show question.
So far everything I have tried just returns results where the answer was 1, 4 or 5 for either User-1 or User-2. This is returning more results than is desired because I cannot figure out how to write the conditions where answer values for 1 are linked to User-1, then compared to User-2 answers with a value of 4 or 5 on the same question_ID.
I am not a complete newbie to the database game, but I am also not a full time developer / programmer either. I have never tried to do anything like this and I am a bit lost. I'm sure there is something pretty easy that I just don't have the experience or knowledge to know how to do.
Any help or recommendations would be great. Thanks!