Query designer in SSMS and order of join sequence?

Question

SSMS has a query designer, which I usually avoid, because honestly, I can't really work with it.

Unfortunately, my colleagues do. As I have 30 minutes to spare, I finally want to know whether I am the problem or SSMS.

For example: Let's assume we have a table buildings, a table floors, a table rooms and a mapping table usage_types (room-usage_types).

When I design the query manually, i do this:

select all buildings, 
left join all floors in those buildings
left join all rooms in those floors
left join the room-usage_types mapping table.

Now, when I try to do the same in query designer, it somehow starts with the mapping table, then left joins the rooms, left joins the floors, and then left joins the buildings.

Which is fundamentally flawed, because if there is no entry in the mapping table for a room, it will not return all rooms (this is assuming every room is necessarely in a floor, and every floor is necessarely in a building, but not every room has necessarely a usage-type associated with itselfs [for a certain period of time])...

Now, if I start to modify the joins I get this query

SELECT  
FROM usage_type mapping 
RIGHT OUTER JOIN Floors 
LEFT OUTER JOIN Rooms 
RIGHT OUTER JOIN Floors

Now this query seems equivalent, but this is an example, and usually, i have to join many more tables. So I don't particularly like the mix of right and left joins, because it's hard to understand in the end (especially when several ON statements don't follow at the place where the join is made), making the search for bugs near impossible (and I don't have much trust into query designer's ability to figure out what I want).

I have so far not found a way to 'design' the query like i want. The only way seems to be to create the initial query, then get the SQL text, then adjust the joins, and then reopen this with SQL query designer.

Needless to say that in the end, this takes longer than doing it manually in the first place, and when the SQL script was modified by the query designer, you can't read it anymore without reformatting it first by hand...

It seems to me that my variant is utterly impossible to accomplish with query designer ... What I find peculiar as well is that in the query designer, in the context menu of the join symbol, sometimes the table to the left is on top, and sometimes, the table to the right.

And then, when I have to select the lower one instead of the upper one, it does a right join. I can choose the upper one, then it does a left join, but in the wrong sequence... which is exactly what I don't want...

So I wanted to ask: Is there some 'secret' way of specifying from which table the query shall start, preferably without any right joins at all ?

Answer 1

I agree with Aaron's assessment in the comments but thought I'd have a look anyway using the following table definitions.

CREATE TABLE buildings(
building_id int primary key)

CREATE TABLE floors(
floor_id int primary key,
building_id int references buildings)

CREATE TABLE rooms(
room_id int primary key,
floor_id int references floors)

CREATE TABLE room_usage_types(
room_id int references rooms,
usage_type_id int,
PRIMARY KEY  (room_id,usage_type_id))

Bringing up the "Design Query In Editor" window and adding the 4 tables by holding down the CTRL key whilst clicking I found that it makes a difference what order you select them in.

If you choose in the order room_usage_types, rooms, buildings, Daily then it starts you off with the not very helpful

SELECT     
FROM         rooms INNER JOIN
                      room_usage_types ON rooms.room_id = room_usage_types.room_id CROSS JOIN
                      buildings CROSS JOIN
                      Daily

But if you choose in order buildings, floors, rooms, room_usage_types you end up with the much more promising

SELECT     
FROM         buildings INNER JOIN
                      floors ON buildings.building_id = floors.building_id INNER JOIN
                      rooms ON floors.floor_id = rooms.floor_id INNER JOIN
                      room_usage_types ON rooms.room_id = room_usage_types.room_id

From this starting point do not click to "Select all rows from buildings" as it converts the query to the below.

SELECT     
FROM         rooms INNER JOIN
                      floors ON rooms.floor_id = floors.floor_id INNER JOIN
                      room_usage_types ON rooms.room_id = room_usage_types.room_id RIGHT OUTER JOIN
                      buildings ON floors.building_id = buildings.building_id

Instead work from right to left starting with the diamond between rooms, room_usage_types and choosing "Select all rows from rooms" and so on. This seems to yield the desired result.

SELECT     
FROM         buildings LEFT OUTER JOIN
                      floors ON buildings.building_id = floors.building_id LEFT OUTER JOIN
                      rooms ON floors.floor_id = rooms.floor_id LEFT OUTER JOIN
                      room_usage_types ON rooms.room_id = room_usage_types.room_id