Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Regarding the specific error you have, try changing Company.Name to Cm.Name. –  Sam Jan 6 '13 at 3: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 6 '13 at 3:24
    
You may also be missing the AS keyword before you define the aliases Cm and Pg. –  Sam Jan 6 '13 at 3:30
    
Which DBMS are you running the query against? –  Sam Jan 6 '13 at 3:31
    
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 6 '13 at 7:54
show 1 more comment

1 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'
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.