2 Replies Latest reply on Jan 16, 2019 9:45 AM by Cookiemonster76

    join syntax

    MatiNug

      Hi all,

      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 :

      select *

      from table a left join table b

      on a.a_id=b.b_id

      left join table c d

      on a.a_id =c.c_id

        • 1. Re: join syntax
          Frank Kulash

          Hi,

          MatiNug wrote:

           

          Hi all,

          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 :

          select *

          from table a left join table b

          on a.a_id=b.b_id

          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

          (

              select  ...

              from    ...

              where   ...

          )

          select  ...

          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?

          • 2. Re: join syntax
            Cookiemonster76

            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

             

            SELECT <columns>,

                           (sub-query)

            FROM ....

             

            b) in the where/ON clauses in place of a column

            SELECT ....

            FROM tab1 JOIN tab2 ON ...

            WHERE col IN (subquery)

             

            SELECT ....

            FROM tab1 JOIN tab2 ON col1 = col2 AND col3 IN (subquery)

             

            c) in the from/join clause in place of a table

             

            SELECT ...

            FROM (subquery) a

            JOIN tab1 ON a.col = tab1.col

             

            SELECT ...

            FROM tab1

            JOIN (subquery) a ON a.col = tab1.col

             

            d) In a with clause as Frank demonstrated above