php - 从带有连接表的两个表中选择数据

Question

我对内/左连接有点困惑。

我有 3 张 table :

users
-> id (PRIMARY with auto increment)
-> first_name
-> last_name
-> email
-> status

companies
-> id (PRIMARY with auto increment)
-> company_name
-> email
-> status

link
-> id (PRIMARY with auto increment)
-> user_id
-> company_id
-> status (if the connection is active)

我正在尝试使用链接连接表，然后从相互连接的用户和公司获取所有数据。现在我分三步完成，获取 user_id，然后检查链接表并获取 company_id，然后获取公司信息。

Answer 1

一条 SQL 就可以了。
只需使用公用键将它们加入即可。

SELECT 
 usr.first_name, 
 usr.last_name, 
 usr.email as user_email, 
 usr.status as user_status,
 comp.company_name, 
 comp.email as company_email, 
 comp.status as company_status,
 usrcomp.status as link_status
FROM users AS usr
JOIN link AS usrcomp ON usrcomp.user_id = usr.id
JOIN companies AS comp ON comp.id = usrcomp.company_id
WHERE usrcomp.status = 1