How to filter related objects in mysql

Question

Could anyone help me with this here. I have a user table like this in mysql database:

MariaDB [tiro]> SELECT * FROM user_tb;
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | dias       | maria     |
|  2 | paulo      | heleno    |
|  3 | silas      | raimundo  |
+----+------------+-----------+

And this one for questions: NOTE: It has a foreign key to user_tb.

MariaDB [tiro]> SELECT * FROM question_tb;
+----+-------------------------+-------------+
| id | question                | asked_by_id |
+----+-------------------------+-------------+
|  1 | whats up                |           1 |
|  2 | who is out there        |           1 |
|  3 | where do we party today |           3 |
+----+-------------------------+-------------+

And the other for answers: NOTE: It has foreign key to user_tb and question_tb.

MariaDB [tiro]> SELECT * FROM answer_tb;
+----+---------------------------------+-------------+----------------+
| id | answer                          | question_id | answered_by_id |
+----+---------------------------------+-------------+----------------+
|  2 | the man in control of you house |           2 |              1 |
|  3 | nothing at all                  |           1 |              1 |
|  4 | lets party at my house guys     |           3 |              1 |
|  5 | casino is the best place        |           3 |              2 |
|  6 | its your enemy                  |           2 |              2 |
|  7 | we are fuck this world          |           1 |              3 |
+----+---------------------------------+-------------+----------------+

The tables have all data that i need to filter. Here i want to filter all questions that the user with id 1 have a answer but exclude all answers answered by other users to that question.

User id 1 has a answer at question id 2 and also the user id 2 too. How could i get question id 2 with only answer of user 1 leaving out user 2. is it possible? My brain is locked here, Please help.

Answer 1

I am not sure if I have correctly understood your intentions, but if I have, I believe that the answer is as simple as this:

SELECT *
FROM question_tb
JOIN answer_tb ON answer_tb.question_id = question_tb.id
WHERE answered_by_id = 1; <- replace this with your desired ID

This query just retrieves all existing question-answer pairs and filters out all pairs consisting of an answer from someone else than from the specified user.

Example output:

+----------------+-------------------------+-------------++--------------+---------------------------------+-------------+----------------+
| question_tb.id | question                | asked_by_id || answer_tb.id | answer                          | question_id | answered_by_id |
+----------------+-------------------------+-------------++--------------+---------------------------------+-------------+----------------+
|              1 | whats up                |           1 ||            3 | nothing at all                  |           1 |              1 |
|              2 | who is out there        |           1 ||            2 | the man in control of you house |           2 |              1 |
|              3 | where do we party today |           3 ||            4 | lets party at my house guys     |           3 |              1 |
+----------------+-------------------------+-------------++--------------+---------------------------------+-------------+----------------+

Answer 2

As per your question, you want to get the answer to question 2, but only the answer given by user "dias maria", whose user ID is 1.

A couple of simple "WHERE" clauses can achieve that:

SELECT * 
FROM answer_tb 
WHERE question_id = 2 AND answered_by_id = 1

If you wish to output extra details such as the question text and the user's name, you can join to those tables too:

SELECT * 
FROM answer_tb 
INNER JOIN user_tb ON user_tb.id = answer_tb.answered_by_id
INNER JOIN question_tb ON question_tb.id = answer_tb.question_id
WHERE answer_tb.question_id = 2 AND answer_tb.answered_by_id = 1

This is quite basic SQL so I suggest you do a little more study if this is giving you a headache.