Can I skip the intermediate table in a join?

Question

I've written this query :

SELECT 
    "BD - Utilizadores".Utilizador, 
    "BD - Utilizadores"."Palavra passe", 
    "BD - Áreas".área 
FROM "BD - Utilizadores" 
INNER JOIN "BD - Permissões" 
    on "BD - Utilizadores".id = "BD - Permissões"."user_id" 
Join "BD - Áreas" 
    on "BD - Permissões".area_id = "BD - Áreas".id

I want to join data from table "bd - utilizadores" with "bd - áreas". Since they have no direct link up I had to use a "man in the middle" i.e. "bd - Permissões". Here is the diagram:

My main question is, is there any other way to do this and get the same result?

Answer 1

tl;dr

The short answer is no. You have to do the multiple join, even if you only want columns from the two tables 'on each side'.

Table design

This seems like a pain, but although it makes your queries longer, it is much more important to get the database structure, including foreign keys right, than it is to make queries short.

There are a couple of ways of arranging foreign keys, sometimes with extra tables which are only there to express relations. For example, if every Cat has a unique Mouse, there could be a foreign key from Cat to Mouse with a unique constraint. It could even be the primary key of Cat. If every Cat has zero or more Mouses, but no Mouse having more than one Cat, you would have a usual foreign key from Mouse to Cat. If every Cat has zero or more Mouses, and every Mouse has zero or more Cats, you would create a third table CatMouseMatching, which has an entry for each 'edge' between a Cat and a Mouse, with foreign keys to the Cat and the Mouse. You will learn that these patterns are very important and the basic cases cover a lot of situations.

Now, suppose that you reverse the foreign keys in your table design, so that Utilizadors and Areas both have a foreign key to Permissoes. This would mean that you could simplify the join - because you could filter on permissoe_id being equal in both tables. However, this would be a quite strange relationship between Utilizador and Area which might not correspond to reality. You might have an Utilizador associated with a Permissoe which did not have any Area linked to it. This Utilizador would just disappear from the inner join.

This might only make sense if there was some real entity which formed the link between the two entities. For example, every Cat lives in a House, and every Mouse lives in a House. You can easily find the Cat-Mouse pairs which live in the same House.

Practical tips

You could always create a VIEW, which makes a common query act like a table. Your query above could be defined as the view, and then other queries could select from it and join to it, without having to understand what joins make up the view internally.

If you don't create a view, you might at least save the snippet of code which does the join, so that you can paste it into other similar queries later.

Answer 2

If that is the result you want (all combinations of permissions set between for users for area) then no.

If you are looking to list all areas and users with their respective permissions including where no permission is set then you need to CROSS JOIN those table then LEFT OUTER JOIN the permissions table to both.

As a side note: it may be beyond your control if it is someone else's DB but I would avoid spaces in table names where possible, and non-ANSI characters too. That will make the code easier to read (though that is of course subjective opinion), avoid the need to escape the names with quotes or brackets, and reduce problems later if you need to access the DB with tools that don't support such things in names or need to migrate the database to another platform that doesn't.

Answer 3

I think you may be mixing up syntax and semantics. It is syntactically correct to join any two tables on any columns that are type compatible. For example, you could join Utilizadores and Áreas on email = area. It would be legal SQL to do so but it would be meaningless. In other words, syntactically valid but semantically invalid.

The table Permissões is there for a reason. Its existence records meaning. A row in that table connects a user to an area for a reason. It may be that user manages that area, or sells into that area or is banned from entering that area or any other possibility. The real-world situation that your database models will determine what the meaning is. There may be several reasons to join two things and those reasons should be modeled separately. Take the example of a person and a car. The same person may be the registered owner of the car, he may have paid for it and he may be driving it right now. Those are three different facts, however, none imply the other, each have different semantics and should be modelled separately.

By joining to Permissões you are using the semantics built into that table. Any other join will have different semantics, so even if it returns the same values it will have different meaning and so will not be equivalent.