Can I write the T-SQL like below
select *
FROM (select *
from TableA
where FieldA = 1
)
where FieldB > 10
which means I want to query from the results of another query.
views:
38answers:
3
+2
A:
Yes you can
select *
FROM ( select * from TableA where FieldA=1 ) sub
where FieldB > 10
Just remember to give the sub select an alias.
astander
2010-02-24 05:13:26
Not much point in the alias if you don't use it ;)
OMG Ponies
2010-02-24 05:20:13
Morning Ponies. The query will not compile if you leave out the table alias... X-)
astander
2010-02-24 05:22:14
I've only had that experience with MySQL - gonna test that tomorrow
OMG Ponies
2010-02-24 05:22:59
Just did it on my PC... **Msg 156, Level 15, State 1, Line 12 Incorrect syntax near the keyword 'WHERE'.**
astander
2010-02-24 05:23:20
Morning?! Ah, cuz you're in South Africa. It's 9ish PM here in Canada
OMG Ponies
2010-02-24 05:25:42
Thanks, that why I always get an error.
Yongwei Xing
2010-02-24 05:27:39
A:
If you want to separate out your sub-queries you can also use Common Table Expressions (CTE's) which help make your code more readable:
WITH Foo (FieldA, FieldB, FieldC) AS
(
SELECT FieldA, FieldB, FieldC
FROM TableA
WHERE FieldA=1
)
SELECT *
FROM Foo
WHERE FieldB > 10
The downside is you will have to explicitly name your columns. However, this actually makes your code faster so it's not always a bad thing.
Joe Swan
2010-02-24 09:54:04