CODEWITHSUNDEEP
sql-server
Derin
Derin

Reputation: 2185

Using where condition in sql query

I have an sql query like this

Select col1, (select abc from table2 where def=1) as col2
From Table1 inner join table3 on Table1.id = table3.id
Where col2 = 4

The problem is that the where condition doesn't work. I get an error saying

Invalid column name 'col2'

Kindly help me fix this sql query.

Thanks in advance

Upvotes: 1

Views: 131

Answers (2)

Martin Smith
Martin Smith

Reputation: 453278

You can define it in a CROSS APPLY and then reference in the SELECT and WHERE

SELECT col1,
       col2
FROM   Table1
       INNER JOIN table3
         ON Table1.id = table3.id
       CROSS APPLY (SELECT abc
                    FROM   table2
                    WHERE  def = 1) C(col2)
WHERE  col2 = 4

Upvotes: 4

simon
simon

Reputation: 3530

Using a CTE (Common Table Expression):

WITH SubQuery AS (Col2) {
    SELECT 
        ABC
    FROM
        table2
    WHERE
        def = 1
}
SELECT
    T.Col1,
    S.Col2
FROM
    SubQuery S,
    Table1 T
    INNER JOIN table3 t3
        ON T.id = t3.id
WHERE
    S.Col2 = 4

Although I must say I agree with the first comment - this makes no sense since your subquery is not correlated (joined) to the rest of your query...

Upvotes: 0

Related Questions