CODEWITHSUNDEEP
sqljoinsubquery
Ismail K. Wahdan
Ismail K. Wahdan

Reputation: 53

How to nest joins of subqueries in SQL?

I am trying to implement an automatic query generator, and my approach for one kind of queries is to have nested joins in such way Select * From (( Q1 join Q2 on Q1.x = Q2.x) As T1 Join Q3 on T1.x=Q3) As T2) Join Q4 on T2.x = Q4.x ) As T3 ... and so on

I tried to implement it in such way but the syntax seem to be wrong 

SELECT
  *
FROM
  (
    (
      SELECT
        *
      FROM
        (
          Select
            *
          From
            Customers
        ) AS q11
        INNER JOIN (
          Select
            *
          From
            Customers
        ) AS q12 ON q11.customerID = q12.customerID
    )
  ) q1
  JOIN (
    Select
      *
    from
      Customers
  ) q2 ON q1.q11.CustomerID = q2.CustomerID

Upvotes: 0

Views: 236

Answers (3)

philipxy
philipxy

Reputation: 15148

Select statements & subqueries do not return columns with dots.

So q1.q11.CustomerID makes no sense.

An SQL JOIN calculates a cross join. That value first has a column per left table column, with the names of the left table columns; then that value has a column per right table column, with the names of the right table columns. The columns are identified by dotted column names aka column references, but their names have no dots. A SELECT * returns all those columns--so it returns duplicate names when input tables share names. But not dots in names.

Upvotes: 2

Rachit
Rachit

Reputation: 15

A query in the from clause is suitable in creating tables.

select * from ( select * from ( select * from ( select * from q1,q2 where q1.x=q2.x ) t1 , q3 where t1.x=q3.x ) t2 , q4 where t2.x=q4.x ) t3 , q5 where t3.x=q5.x

Upvotes: 0

Ross Bush
Ross Bush

Reputation: 15185

A little more compact version would be:

;WITH Data AS(Select * From Customers)

SELECT 
    * 
FROM
    Data D1
    INNER JOIN Data D2 ON D2.ID=D1.ID
    INNER JOIN Data D3 ON D3.ID=D2.ID
    INNER JOIN Data D4 ON D4.ID=D3.ID
    INNER JOIN Data D5 ON D5.ID=D4.ID
    INNER JOIN Data D6 ON D6.ID=D5.ID
    INNER JOIN Data D7 ON D7.ID=D6.ID

And even more compact:

SELECT * FROM D D1
JOIN D D2 ON D2.ID=D1.ID
JOIN D D3 ON D3.ID=D2.ID
JOIN D D4 ON D4.ID=D3.ID
JOIN D D5 ON D5.ID=D4.ID
JOIN D D6 ON D6.ID=D5.ID
JOIN D D7 ON D7.ID=D6.ID

Upvotes: 2

Related Questions