SQL question on Video 3 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL question on Video 3

Author

Topic

NewToSQLserver
Starting Member

6 Posts

Posted - 2011-01-14 : 00:26:48

On Video 3 of Database developement Series. There is a nested query:
My question is on the first line of this query. We have a select ordenumber. Are we refering here to the ordernumber that belongs to the Orders Table or the ordenumber that belongs to OrderDetails Table.
In other words, is it Orders.ordernumber Or OrderDetails.ordernumber?

select ordernumber as "Order Number", firstname as "First",
lastname as "Last", names.CommonName as "Flower"
from sales.Customers as c,
(select ords.CustomerNumber, flowernames.OrderNumber,
FlowerNames.CommonName from SALES.Orders as ords,
(select CommonName, so.OrderNumber
from SALES.Flowers as sf , SALES.OrderDetails as so
where sf.FlowerID = so.FlowerID) as FlowerNames
where ords.OrderNumber = FlowerNames.OrderNumber) as names
where c.CustomerNumber = names.CustomerNumber
order by lastname, firstname, ordernumber

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-14 : 01:27:21

I think this query would give an error ambiguous column name ordernumber...
But it would also give an error because of a missing ) before "as ords"...

No, you're never too old to Yak'n'Roll if you're too young to die.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-14 : 01:35:26

My question is on the first line of this query. We have a select ordenumber. Are we refering here to the ordernumber that belongs to the Orders Table or the ordenumber that belongs to OrderDetails Table.

If this Column exists in both tables then the SQL will be confused same as that you are :D and will give you the error Webfred mentioned!~

Despite that there seems to be errors in the following Section

(select ords.CustomerNumber, flowernames.OrderNumber,
FlowerNames.CommonName from SALES.Orders as ords,

(select CommonName, so.OrderNumber
from SALES.Flowers as sf , SALES.OrderDetails as so
where sf.FlowerID = so.FlowerID) as FlowerNames --not sure exactly but this will give you an error too

where ords.OrderNumber = FlowerNames.OrderNumber) as names -- This "where" belongs to which From Clause?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources