I'm writing because I have difficulty understanding how a subquery works within a join
for example in the following query how can i add a subquery :
from table a left join table b
left join table c d
on a.a_id =c.c_id
There are lots of different kinds of sub-queries, that do lots of different things. What, exactly, do you want to do? Why do you think a sub-query is the way to do it?
One common use of sub-queries makes the result set of the sub-query act like a table. Here's one way to do that:
with sub_q as
from table_a a
left join table_b b on a.a_id = b.b_id
left join table_c d on a.a_id = c.c_id
left join sub_q s on ...
Again, what are you trying to do?
See the Forum FAQ: Re: 2. How do I ask a question on the forums?
You can add sub-queries in all the same ways you would add them to a select that doesn't use JOIN syntax:
a) in the select part in place of a column
b) in the where/ON clauses in place of a column
FROM tab1 JOIN tab2 ON ...
WHERE col IN (subquery)
FROM tab1 JOIN tab2 ON col1 = col2 AND col3 IN (subquery)
c) in the from/join clause in place of a table
FROM (subquery) a
JOIN tab1 ON a.col = tab1.col
JOIN (subquery) a ON a.col = tab1.col
d) In a with clause as Frank demonstrated above