stack stack - 6 months ago 8
PHP Question

How to select related columns by having just an id number?

I have this table:

// mytable
+----+---------+
| id | related |
+----+---------+
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 1 |
| 5 | 3 |
| 6 | 2 |
| 7 | 4 |
| 8 | 4 |
| 9 | 2 |
+----+---------+


I have just an id number like
$id = 6
. And I want to select all rows which have identical
related
. So this is expected result:

// newmytable
+----+---------+
| id | related |
+----+---------+
| 3 | 2 |
| 6 | 2 |
| 9 | 2 |
+----+---------+


How can I do that?

Here is my query but doesn't work:

SELECT *
FROM mytable m
WHERE (SELECT related
FROM mytable
WHERE id = :id) t m.related = t.related

Answer
select m2.id, m2.related
from 
mytable m1
join mytable m2
on m2.related = m1.related 
where m1.id = 6

SQL Fiddle sample

Comments