Join multiple tables on one specific column

Question

I am still new to SQL so this question might sound quite basic. I am using an Oracle database and I can only use select. I have 2 tables Names and Worksite.

Names table:

ID  Full name   Type
-----------------------
1   Sibelga     Company
2   Belgacom    Company
3   Brussels    Authority
4   Etterbeek   Authority

Worksite table:

ID Worksite CompanyID   AuthorityID
-----------------------------------
12569        1              3
4563         2              4
1589         1              4
1489         1              3

Basically, I want to get the full name of the Authority and Company for each worksite.

I'm trying to do a join but it only gives back the name of the Authority or of the Company. I'm trying the following query but it doesn't work, any idea where I'm going wrong?

select  
    ID worksite, 
    Full name, 
    Full name
from    
    Worksite
join    
    Names on worksite.companyID = names.ID

Answer 1

If you need to get two names then you'll need to join to the table two times:

SELECT
    WS."ID worksite",
    C."Full name" AS CompanyName,
    A."Full name" AS AuthorityName
FROM
    Worksite AS WS
LEFT OUTER JOIN Names C ON C.ID = WS.CompanyID
LEFT OUTER JOIN Names A ON A.ID = WS.AuthorityID

I used LEFT OUTER JOIN because you don't mention if the IDs will always be filled in or not. If they will always be there then you can use INNER JOIN. I'm also pretty wary of a table called, "Names". That's generally a red flag for a poorly designed database.