Pulling out my hair!

[Tronsliver]

I have two tables:

employee( employee-name, street, city) - PK employee-name
manages(employee-name, manager-name) - FK employee name

I need to find all employees in the DB who live in the same cities and on the same street as do their managers.

Obviously managers are also employees so its gets wierd! I've tried multiple SQL options but can't get it to work. I would post what I tried but from my results I'm not even close. I can get the employee and manager names to come up but I'm having no luck at all with the street and city name comparisions? Sure would appreciate some clues...

Thank you

 

[onpnt]

sounds like a basic INNER JOIN on the two conditions. Is that the methods you've been trying at? Can you post what you've got thus far

___________________________________________________________________

[sub]

http://www.onpntwebdesigns.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page[/sub]

 

[r937]

it's actually a three-table join, and it's pretty tricky if you've never worked with a hierarchy before

but whoa, it sure sounds like homework, and student postings are not allowed

rudy

http://r937.com/

SQL Consulting

 

[BNPMike]

There are actually two foreign keys in "Manages". "Manages" is an intersection entity. So take out your relationships which I bet you've put in then think 'Manager manages employee' so join employee in employee to manager in manages and employee in manages to employee in employee. You'll now have two sets of address details to compare.