Select columns from 4 tables

Question

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.

Regarding the specific error you have, try changing Company.Name to Cm.Name. — Sam, Jan 06 '13 at 03:22
I'm finding it difficult to understand exactly what you're trying to do. Are you saying that you want to be able to list all of the companies, programmers, managers and CEOs who are in a given city? — Sam, Jan 06 '13 at 03:24
You may also be missing the AS keyword before you define the aliases Cm and Pg. — Sam, Jan 06 '13 at 03:30
You have companies located in cities, you also have programmers,managers and CEOs located in cities. Now you want a list of the names of all the companies, programmers, managers and CEOs that are located in London? — miracle173, Jan 06 '13 at 07:54
The error for the second select stems from the fact that you assigned an alias for the company table, therefor you need to use that alias in the select list as well (not only in the join and where conditions): select Cm.Name ... — , Jan 06 '13 at 11:49

Taryn · Answer 1 · 2013-01-06T05:18:13.910

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'

Select columns from 4 tables

1 Answers1