SQLite join selection from the same table using reference from another table
- by daikini
I have two tables:
table: points
|key_id | name | x | y |
------------------------
|1 | A |10 |20 |
|2 | A_1 |11 |21 |
|3 | B |30 |40 |
|4 | B_1 |31 |42 |
table: pairs
|f_key_p1 | f_key_p2 |
----------------------
|1 | 2 |
|3 | 4 |
Table 'pairs' defines which rows in table 'points' should be paired.
How can I query database to select paired rows?
My desired query result would be like this:
|name_1|x_1|x_2|name_2|x_2|y_2|
-------------------------------
|A |10 |20 |A_1 |11 |21 |
|B |30 |40 |B_1 |31 |41 |