如何从三个表中选择特定数据？[PostgreSQL 9.1]

Question

# select * from users ;
 user_id | login  |          email          | password | firstname | lastname 
---------+--------+-------------------------+----------+-----------+----------
       1 | katie  | [e-mail]       | secret   | Katie     | Secret
(1 row)

# select * from forum;
 forum_id | forum_name | group_id | forum_parent 
----------+------------+----------+--------------
         1|  1st forum |        1 |            -1
(1 row)

# select * from groups;
 group_id |      group_name       |                group_description                | group_founder 
----------+-----------------------+-------------------------------------------------+---------------
        1 | Java programming      | System.out.println("Hello world in Java!"); :)  |             1
(1 row)

我有3个数据表，我想从中获取由用户id = 1创建的论坛。如何做到这一点？（在groups表中，创建此类组的用户id称为group_founder）。

我的意思是，通过上面的输出，您可以看到id = 1的用户创建了一个名为“Java编程”的组，然后在该组内创建了一个名为“第一个论坛”的论坛。请帮忙:)

- Katie

Answer 1

试试这个

SELECT *
FROM forum f
JOIN groups g ON g.group_id = f.group_id
JOIN users u ON u.user_id = g.group_founder 
WHERE u.user_id = 1

- Hary

Answer 2

根据您的输入，查询可能是这样的：

SELECT U.login,U.email,U.firstname
FROM users U JOIN groups G ON U.user_id = G.group_founder
             JOIN forum F ON G.group_id = F.group_id
WHERE U.user_id = 1

DEMO

- Muhammad Hani

---

@Katie - 很高兴能帮助到你 :) - Muhammad Hani