I have a problem that I've racked my brain with for two weeks without success.
I have a table named 'user_answers' containing columns 'user_answer_id'
and 'user_name'. Each 'user_name' has 25 rows. I'm trying to compare/count
the number of rows where user A and user B match answer_id.

       SELECT COUNT (*) user_answer_id  FROM  user_answers  WHERE
        user_name = Jake  LIKE    (SELECT  user_answer_id FROM  user_answers  WHERE user_name 
         = Betty) 

The above returns 25 rows from Jake. There should only be 6. I've tried substituting LIKE with IN but get every row in the table.

Any help with this would surely be appreciated.

Recommended Answers

All 4 Replies

You'll need to do a self join. Can you give an sql dump of the structure and data?

Basically it's: select all jake's answers and join them with all betty's answers.

commented: Found a spot to up-vote you. :) +3

Ok, thanks. Yes, I've read alot about joins but still have no idea about how to do that. I've tried for a week to get a subquery statement to work various ways because at least I can grasp the concept.
I'm attaching a dump of Table user_answers. But basically its like this.

Table = user_answers

  | user_answer_id | user_name |
  | 177034         | Betty     |
  | 177037         | Betty     |
  | 177040         | Betty     |
  | 177035         | Jake      |
  | 177040         | Jake      |
  | 177045         | Jake      |

  There are 25 user_answer_id per user. When answering a question they have anywhere from 2 to 4 answers to choose from. I'm just trying to retrieve the user_answer_id count between Betty and Jake that exactly match.
select * 
from user_answers ua1, user_answers ua2
where ua1.user_name = 'Jake'
and ua2.user_name = 'Betty'
and ua1.user_answer_id = ua2.user_answer_id

I can't thank you enough! I'm ashamed to say exactly how long I've been trying to accomplish this. I prefer to figure things out for myself but this was way over my head. The ua1 and ua2 was what was confusing. But now that I have a working example I think I can figure it out if the need ever arrises again. Thank You!!! Thank You!!! Thank You!!!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.