CODEWITHSUNDEEP
sqlsql-server-2005
Francis Ducharme
Francis Ducharme

Reputation: 4987

Cannot query an alias table

I'm doing something like:

SELECT T1.NAME, T2.DATE
    FROM T1
INNER JOIN
(
    SELECT * FROM OTHERTABLE
) AS T2 ON T2.USERID = T1.USERID

Which works, but if I query the alias table, I get an error saying that T2 is an invalid object name.

Example:

SELECT 
T1.NAME, 
T2.DATE,
CASE
    WHEN EXISTS (SELECT TOP 1 1 FROM T2 WHERE T2.THISFIELD = T1.THISFIELD) THEN 'HELLO'
    ELSE 'BYE'
END AS COMMENT -- THIS ALSO FAILS
    FROM T1
INNER JOIN
(
    SELECT * FROM OTHERTABLE
) AS T2 ON T2.USERID = T1.USERID
WHERE (SELECT COUNT(*) FROM T2) > 0

I thought that's what I did, "create" T2. Is there any way I can use T2 like such ?

My goal is to scrape all the related data from OTHERTABLE once because I'll have many CASE in the SELECT clause depending whether data exists in T2 or not. I don't want to do EXISTS for every field since that'll launch a new query in a huge table everytime.

Upvotes: 0

Views: 65

Answers (1)

Kaf
Kaf

Reputation: 33809

Your query using a sub-query of SELECT * FROM OTHERTABLE which doesn't make sense. You can modify it like;

SELECT 
T1.NAME, 
T2.DATE,
...

FROM T1
    JOIN OTHERTABLE T2 ON T2.USERID = T1.USERID
WHERE (SELECT COUNT(*) FROM OTHERTABLE ) > 0

You cannot use a sub-query multiple times in the same query. Instead use a Common Table Expression (CTE) for that purpose. T2 is a CTE in the following example.

;WITH T2 AS
(
   SELECT UserId, col1, col2, [Date]
   FROM OtherTable
)
SELECT T1.NAME, T2.DATE
FROM T1
    JOIN T2 ON T2.USERID = T1.USERID
WHERE (SELECT COUNT(*) FROM T2) > 0

Upvotes: 1

Related Questions