Using where condition in sql query

Question

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

Is that your actual query? Seems odd. The sub query isn't correlated. — Martin Smith
– Martin Smith, Commented Feb 2, 2012 at 16:05

Martin Smith · Accepted Answer · 2012-02-02 16:17:12Z

4

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

edited Feb 2, 2012 at 16:17

answered Feb 2, 2012 at 16:06

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Martin Smith Over a year ago

@ypercube - Yes. I was thinking that there would be a difference if the sub query could return 0 rows rather than 1 (and that maybe I should use OUTER APPLY instead) but the WHERE clause takes care of that.

Derin Over a year ago

@MartinSmith - Thanks. Can I use multiple queries in CROSS APPLY? What if I need a col3 similar to col2.

Martin Smith Over a year ago

@Derin - The SELECT in the CROSS APPLY can return multiple columns and you can have multiple levels of CROSS APPLY. If you were to show your actual query there may well be simpler or more efficient ways though.

simon · Accepted Answer · 2012-02-02 16:12:45Z

0

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...

answered Feb 2, 2012 at 16:12

simon

3,6902 gold badges26 silver badges24 bronze badges

Collectives™ on Stack Overflow

Using where condition in sql query

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related