CODEWITHSUNDEEP
sqlt-sql
Lucas_Santos
Lucas_Santos

Reputation: 4740

Call one CTE in another CTE

How could I call a CTE in another CTE ?

WITH cte1
AS (
    SELECT City.*
    FROM City
    WHERE (City.CityName COLLATE SQL_Latin1_General_CP1_CI_AI) LIKE 'são paulo'
)
, cte2
AS (
    SELECT Imovel.Imovel_Id 
    FROM Imovel
    WHERE Imovel.Number = 311
    AND Imovel.ZIPCode = '30280490'
    AND Imovel.Complement = ''
    AND Imovel.Street = 'Do furquim'
        -- the line below has an error in cte.City_Id
    AND Imovel.City_Id = cte1.City_Id 
)

Upvotes: 10

Views: 19502

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460238

You have to join both like with a normal table:

WITH cte1 
     AS (SELECT city.* 
         FROM   city 
         WHERE  ( city.cityname COLLATE sql_latin1_general_cp1_ci_ai ) LIKE 
                'são paulo'), 
     cte2 
     AS (SELECT imovel.imovel_id 
         FROM   imovel 
                INNER JOIN cte1 
                        ON imovel.city_id = cte1.city_id 
         WHERE  imovel.number = 311 
                AND imovel.zipcode = '30280490' 
                AND imovel.complement = '' 
                AND imovel.street = 'Do furquim') 
SELECT * FROM   cte2

Note that i have appended SELECT * FROM cte2 since CTE's cannot "stand" alone.

Upvotes: 22

Related Questions