Select columns from 4 tables

select

First thing, I am totally new to SQL. I saw some tutorials, videos, tried searching but didn't find what I needed. I know it has asked before, but I couldn't find it.

What I need to find is basically like:

Select 
    Company.Name, Programmer.Name, Manager.Name, CEO.Name
where 
    Company.City = Programmer.City = Manager.City = CEO.City

like select all of them who are from London.

EDIT: Tried doing this with 2 tables only, code as below, but I'm getting an error:

SELECT Company.Name
FROM Company Cm
INNER JOIN Programmer Pg ON Cm.City = Pg.City
WHERE Cm.City = 'London'

The multi-part identifier "Company.Name" could not be bound.

Best Answer

If you are attempting you return all rows from the four tables where the City is the same, you should be able to use a UNION ALL to get the results:

select name, 'Company' as Source
from company
where city = 'London'
union all
select name, 'programmer' as Source
from programmer
where city = 'London'
union all
select name, 'manager' as Source
from manager
where city = 'London'
union all
select name, 'ceo' as Source
from ceo
where city = 'London'

I included a field that identifies what table the record is coming from. This is not necessary for the query and can be removed.

Just as a side note, the error you were getting in your query is because you created an alias for the Company table but when selecting the Company.name in the SELECT list you are not using the alias. You would need to use:

SELECT Cm.Name
FROM Company Cm
INNER JOIN Programmer Pg
   ON Cm.City = Pg.City
WHERE Cm.City = 'London'

If you want to JOIN the tables then you could use something similar to this:

SELECT Cm.Name
FROM Company Cm
INNER JOIN Programmer Pg
   ON Cm.City = Pg.City
INNER JOIN Manager M
   ON Cm.City = M.City
INNER JOIN CEO C
   ON Cm.City = C.City
WHERE Cm.City = 'London'
Related Question