Multiple joins in a SQL query

Question

Question

CJ H · Oct 14, 2017

#SQL #Caché

HI,

I have a query like below but its syntax is not accepted by Cache.

I would like to perform a left join on two tables first and then make a inner for this result with another table.

Given the constraint that we only allow one SELECT in the query, it is possible to achieve this semantics ?

Thank for your help.

Select *
FROM ( sample . employee e
LEFT JOIN sample . company c
on c . id = e . id ) g
JOIN sample . vendor v
on v . %id = g . attr

Discussion (3)0

Log in or sign up to continue

Randy Shaw · Oct 14, 2017

I am not sure there is a logical way for those tables to be joined. But for syntax purposes here is a query that works although, as mentioned, illogically.

SELECT
* FROM (SELECT * FROM Sample.Employee e LEFT JOIN Sample.Company c ON c.ID=e.Company) g
JOIN Sample.Vendor v ON g.Company=v.Vendor

0 0

score 1 · Answer 1 · 2017-10-14T16:21:56-04:00

That does not seem to be possible. Why not JOIN them directly?

SELECT *
FROM Sample.Employee e
LEFT JOIN Sample.Company c ON c.id = e.id
JOIN Sample.Vendor v ON v.%id = c.attr

score 0 · Answer 2 · 2017-10-15T09:27:24-04:00

Thanks, that would work, however, I doubt its correctness.

I am kind of confused about the how Cache SQL handles the associativity between the different the join types.

If every join is inner join, that's fine, we could start from any table as the initial table to do the join.

However, in my query, there is one left join and one inner join.

In this case, I don't expect Sample.Company could be the initial table to perform the join.

Below is the plan Cache SQL output:

• Read master map Sample.Company.IDKEY, looping on ID.

• For each row: - Read master map Sample.Employee.IDKEY, using the given idkey value.

- Read master map Sample.Vendor.DataMasterMap, using the given idkey value.

- Output the row.

Any comment on that ? Thank for your help.