select within select

Question

Post reply

select within select

dr_csharp

SSCommitted

Points: 1765
More actions
October 7, 2008 at 11:39 am

#198953

whats wrong with this 😕

SELECT A,B FROM (SELECT * FROM Table WHERE Id>5)

it has syntax error 🙁

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Garadin

One Orange Chip

Points: 29613

October 7, 2008 at 11:54 am

#881865

The word "Table" is a keyword. Try using something like MyTable instead.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 2

TheRedneckDBA

SSChampion

Points: 14001

October 7, 2008 at 12:01 pm

#881868

You might also need to give that inner query an Alias?

SELECT Tab.A,Tab.B FROM (SELECT * FROM Table) Tab

The Redneck DBA

dr_csharp SSCommitted Points: 1765 More actions · Answer 3

dr_csharp

SSCommitted

Points: 1765

October 7, 2008 at 12:50 pm

#881902

Jason Shadonix (10/7/2008)

You might also need to give that inner query an Alias?

SELECT Tab.A,Tab.B FROM (SELECT * FROM Table) Tab

thanks Jason you are right,but why we do this ?

i see most example of select within select in internet like select * from (select ..) without alias !?

Sergiy SSC Guru Points: 110192 More actions · Answer 4

Sergiy

SSC Guru

Points: 110192

October 7, 2008 at 2:24 pm

#881968

Those examples must be not about SQL Server.

_____________
Code for TallyGenerator

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

dr_csharp (10/7/2008)

Jason Shadonix (10/7/2008)

You might also need to give that inner query an Alias?

SELECT Tab.A,Tab.B FROM (SELECT * FROM Table) Tab

thanks Jason you are right,but why we do this ?

i see most example of select within select in internet like select * from (select ..) without alias !?

SQL requires this because datasources need names so that you can refer to their columns and distinguish between columns of the same name from multiple datasources. Tables and Views already have names, but derived tables (like your subquery) do not, so you have to give it a name by using the alias syntax.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

dr_csharp (10/7/2008)

Jason Shadonix (10/7/2008)

You might also need to give that inner query an Alias?

SELECT Tab.A,Tab.B FROM (SELECT * FROM Table) Tab

thanks Jason you are right,but why we do this ?

i see most example of select within select in internet like select * from (select ..) without alias !?

The only "unnamed" subselects that are allowed within a SELECT don't live within the FROM clause. The two examples I can think of are the IN clause

...WHERE columnname IN (subquery) ....

or certain correlated sub-queries that appear in the SELECT portion of the statement.

SELECT myfield, (select count(*) from mytable2 b where a.id=b.id)

from MyTable a

The FROM clause however will require an alias.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

dr_csharp SSCommitted Points: 1765 More actions · Answer 7

Matt Miller (10/7/2008)

dr_csharp (10/7/2008)

Jason Shadonix (10/7/2008)

You might also need to give that inner query an Alias?

SELECT Tab.A,Tab.B FROM (SELECT * FROM Table) Tab

thanks Jason you are right,but why we do this ?

i see most example of select within select in internet like select * from (select ..) without alias !?

The only "unnamed" subselects that are allowed within a SELECT don't live within the FROM clause. The two examples I can think of are the IN clause

...WHERE columnname IN (subquery) ....

or certain correlated sub-queries that appear in the SELECT portion of the statement.

SELECT myfield, (select count(*) from mytable2 b where a.id=b.id)
from MyTable a

The FROM clause however will require an alias.

whats wrong with this :



Select * from Callrecorder where RecId between (select RecId from Table2 ) tempTable

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

dr_csharp (10/8/2008)

whats wrong with this :

Select * from Callrecorder where RecId between (select RecId from Table2 ) tempTable

Firstly, as mentioned earlier, a table alias ('tempTable') is only required if the table is referenced in the FROM clause. Your subquery is referenced in the WHERE clause.

Secondly, BETWEEN requires two arguments. Without testing, something like this would work in theory:

SELECT * FROM Callrecorder



WHERE RecId BETWEEN (SELECT RecId FROM Table2 WHERE SomeWhereClause)

AND (SELECT RecId FROM Table2 WHERE SomeOtherWhereClause)

In practice, you'd assign the two RecId's to variables. You would, wouldn't you? 🙂

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden