Select data from two tables with connecting table

Question

I'm a bit confused with inner/left join.

I've got 3 tables:

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)

I am trying to use the link connection table and then grab all data from users and companies that are connected with each other. Right now I am doing that in 3 steps, get the user_id then check link table and grab the company_id and then get the companies info.

Just learn to use proper `JOIN` syntax. This is a SQL fundamental, so it really isn't appropriate for a question here. — Gordon Linoff, Feb 10 '19 at 19:28
About the confusion between INNER versus LEFT join, [here](https://stackoverflow.com/a/38578/4003419) is an old SO post about that. — LukStorms, Feb 10 '19 at 20:10

LukStorms · Accepted Answer · 2019-02-10T19:48:13.120

One SQL will do.
Just join them on the common keys.

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

Select data from two tables with connecting table

1 Answers1