Non Standard SQL query  
Author Message
LauraGuffey





PostPosted: Fri Oct 08 02:25:04 CDT 2004 Top

SQL Server Developer >> Non Standard SQL query

I would like to form query like this:

SELECT Account, Name FROM
(
SELECT BankAccount AS Account
FROM BankAccounts WHERE BankAccountID = ( SELECT BankAccountId
AS Account
FROM OrderDetails
WHERE OrderID = 1005
),
SELECT Authority AS Name
FROM Authority
)

But I always get error. Can sombody help?
Thank you.

SQL Server214  
 
 
JohnBell





PostPosted: Fri Oct 08 02:25:04 CDT 2004 Top

SQL Server Developer >> Non Standard SQL query Hi

Please post DDL and example data http://www.aspfaq.com/etiquette.asp?id=5006

A derived table would require a alias even if you do not reference it, but
it is not clear how your authority table is related to this it may be
possible to do this in two subselects

Here is a derived table and sub-select example:

SELECT Account, (SELECT Authority AS Name FROM Authority ) AS Name
FROM
( SELECT BankAccount AS Account
FROM BankAccounts WHERE BankAccountID = ( SELECT BankAccountId
AS Account
FROM OrderDetails
WHERE OrderID = 1005 ) A

John



> I would like to form query like this:
>
> SELECT Account, Name FROM
> (
> SELECT BankAccount AS Account
> FROM BankAccounts WHERE BankAccountID = ( SELECT BankAccountId
> AS Account
> FROM OrderDetails
> WHERE OrderID = 1005
> ),
> SELECT Authority AS Name
> FROM Authority
> )
>
> But I always get error. Can sombody help?
> Thank you.
>
>
>
 
 
Tibor





PostPosted: Fri Oct 08 02:27:21 CDT 2004 Top

SQL Server Developer >> Non Standard SQL query Also, I don't know what that last SELECT statement is supposed to do?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/



>I would like to form query like this:
>
> SELECT Account, Name FROM
> (
> SELECT BankAccount AS Account
> FROM BankAccounts WHERE BankAccountID = ( SELECT BankAccountId
> AS Account
> FROM OrderDetails
> WHERE OrderID = 1005
> ),
> SELECT Authority AS Name
> FROM Authority
> )
>
> But I always get error. Can sombody help?
> Thank you.
>
>


 
 
Tibor





PostPosted: Fri Oct 08 02:26:31 CDT 2004 Top

SQL Server Developer >> Non Standard SQL query You need to define a table alias (formally "correlation name") for the inner query:

> FROM OrderDetails
> WHERE OrderID = 1005
> ), AS der -- NOTE THIS!!!
> SELECT Authority AS Name


--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/



>I would like to form query like this:
>
> SELECT Account, Name FROM
> (
> SELECT BankAccount AS Account
> FROM BankAccounts WHERE BankAccountID = ( SELECT BankAccountId
> AS Account
> FROM OrderDetails
> WHERE OrderID = 1005
> ),
> SELECT Authority AS Name
> FROM Authority
> )
>
> But I always get error. Can sombody help?
> Thank you.
>
>


 
 
JohnBell





PostPosted: Fri Oct 08 02:39:02 CDT 2004 Top

SQL Server Developer >> Non Standard SQL query I missed out a extra bracket in the example!

SELECT Account, (SELECT Authority AS Name FROM Authority ) AS Name
FROM
( SELECT BankAccount AS Account
FROM BankAccounts WHERE BankAccountID = ( SELECT BankAccountId
AS Account
FROM OrderDetails
WHERE OrderID = 1005 ) ) A

John



> Hi
>
> Please post DDL and example data http://www.aspfaq.com/etiquette.asp?id=5006
>
> A derived table would require a alias even if you do not reference it, but
> it is not clear how your authority table is related to this it may be
> possible to do this in two subselects
>
> Here is a derived table and sub-select example:
>
> SELECT Account, (SELECT Authority AS Name FROM Authority ) AS Name
> FROM
> ( SELECT BankAccount AS Account
> FROM BankAccounts WHERE BankAccountID = ( SELECT BankAccountId
> AS Account
> FROM OrderDetails
> WHERE OrderID = 1005 ) A
>
> John
>

>
> > I would like to form query like this:
> >
> > SELECT Account, Name FROM
> > (
> > SELECT BankAccount AS Account
> > FROM BankAccounts WHERE BankAccountID = ( SELECT BankAccountId
> > AS Account
> > FROM OrderDetails
> > WHERE OrderID = 1005
> > ),
> > SELECT Authority AS Name
> > FROM Authority
> > )
> >
> > But I always get error. Can sombody help?
> > Thank you.
> >
> >
> >
 
 
Johny





PostPosted: Fri Oct 08 04:19:35 CDT 2004 Top

SQL Server Developer >> Non Standard SQL query Thank you all.

The problem was that two table wasn't related and I wanted to get all the
data using one main select.

This will work:

SELECT (SELECT BankAccount
FROM BankAccounts WHERE BankAccountID = (SELECT BankAccountId AS
Account
FROM OrderDetails
WHERE OrderID = 1005
)) AS Account,
(SELECT AuthorityName
FROM Authority) AS Name