Martin AJ Martin AJ - 3 months ago 6
MySQL Question

How do I get id number from another table?

I have two tables:

qanda
and
comment
. Here is the structure of them:

// qanda
+----+----------+----------------------+---------+------+
| id | title | content | related | type |
+----+----------+----------------------+---------+------+
| 1 | a title | a content | NULL | 0 |
| 2 | | a content | 1 | 1 |
| 3 | a title | a content | NULL | 0 |
| 4 | | a content | 1 | 1 |
| 5 | | a content | 3 | 1 |
+----+----------+----------------------+---------+------+
/* type column: "0" means it is a question, "1" means it is a answer
related column: it contains the id number of its own question
*/

// comment
+----+---------+---------+-----------------+
| id | post_id | user_id | content |
+----+---------+---------+-----------------+
| 1 | 1 | 324523 | a content |
| 2 | 5 | 435243 | a content |
+----+---------+---------+-----------------+


I have just a
id
from
comment
table. This is my current query:

SELECT post_id FROM comment WHERE id = :id


Current output:

// assuming :id = 2
+---------+
| post_id |
+---------+
| 5 |
+---------+


But I also need to select the id of its own question. So this is expected result:

// assuming :id = 2
+-------------+---------+
| question_id | post_id |
+-------------+---------+
| 3 | 5 |
+-------------+---------+


Well how can I do that?

Answer

If I'm understanding correctly, you just need to join the tables together:

SELECT c.post_id, q.related
FROM comment c
    join qanda q on c.post_id = q.id
WHERE c.id = :id
Comments