Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Need to compare multiple columns against one another in MySQL table

Posted on 2015-01-04
5
648 Views
Last Modified: 2015-01-04
Hello,

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!
answers-Table-Structure.jpg
0
Comment
Question by:smauel76
  • 3
5 Comments
 
LVL 24

Accepted Solution

by:
mankowitz earned 500 total points
ID: 40530896
In brush strokes, you want to join the table with itself and proceed from there. It would be really useful to put up some sample data with your expected results. Start with this

select * from answers user1 join answers user2 on (user1.question_id=user2.question_id)
where user1.answer=1 and user2 >= 4
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40530932
I don't think your table structure makes any sense.  Note that int(5) means to display only 5 digits of a 32-bit integer.  It does not limit the amount of storage.  Same for tinyint(1) which is basically 0 thru 9.  http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html
0
 

Author Comment

by:smauel76
ID: 40530956
Thanks for the quick replies.

Here is some sample data for you mankowitz, I attached an excel file as well as an image for reference.  This is the test data that is in the system from answers posted by test users.  All other queries work great except for this one I'm working on now.  The rows in yellow should return as results from the query that I can output to the user on the screen.  All other records should be ignored using this sample data.

To restate so you don't have to read back through my long winded initial post: the goal is to show all the records from the answers table where User-1 answered 1 to a question and User-2 answered 4 or 5 to the same question.

Thanks for taking a look at this.
answers-table-data-image.png
answers-table-data.xlsx
0
 

Author Comment

by:smauel76
ID: 40530995
I ended up getting it to work perfectly the way I need it to using the following query, thanks for the input guys! Thanks mankowitz for getting me going in the right direction. :)

SELECT answers.question_ID, answers.answer_ID, answers.user_ID, answers.answer, questions.question, category.category, user.user_ID, user.spouse_ID, AES_DECRYPT(user.fname, 'Eph521')
FROM questions
INNER JOIN answers ON questions.question_ID = answers.question_ID
INNER JOIN category ON questions.category_ID = category.category_ID
INNER JOIN user ON answers.user_ID = user.user_ID
WHERE (answers.user_ID = '2' AND answers.answer = '1') OR (answers.user_ID = '1' AND (answers.answer = '4' OR answers.answer = '5'))
GROUP BY question_ID HAVING COUNT(1) > 1 ORDER BY category DESC
0
 

Author Closing Comment

by:smauel76
ID: 40530996
mankowitz got me pointed in the right direction focuses back on doing a simple join rather than trying to do a UNION to compare the table with itself.  Very helpful.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Read a remote csv file from a https URL 8 47
Wordpress Pagination 1 28
Log in through ID 5 17
Import text from rows into columns? 12 36
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question