Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-12 : 08:54:06
|
| Craig writes "I have 4 tables.Users, Locations, Cities and Departments.I'm trying to produce a query that will perform a left outer join on Users with Locations and Departments and then a join on Locations and cities....So, Users has FKs for Locations and Departments and Locations has an FK from Cities.Heres what I have so far, but I can't get the join to add in the Cities table...select userName, departmentName, locationName, cityNamefrom users, departments, locations, citieswhere users.departmentID *= departments.departmentID and users.locationsID *= locations.locationsID and locations.cityID = cities.cityID <--- Heres my problem.Can you help??" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-12 : 09:10:48
|
| You need to use ANSI join syntax in SQL Server to do this kind of mixed join. You should completely abandon the *= syntax; it's been marked as obsolete since SQL Server 6.5, and it WILL be dropped from SQL Server in a future version (why it's lasted this long I have no idea)Here's the ANSI JOIN equivalent:SELECT userName, departmentName, locationName, cityName FROM users LEFT JOIN departments ON users.departmentID=departments.departmentIDLEFT JOIN locations ON users.locationsID=locations.locationsID INNER JOIN cities ON locations.cityID = cities.cityIDLook in Books Online for more information on JOIN syntax. |
 |
|
|
|
|
|
|
|
Left Outer Joins