|
I am going through the msdn sql book and am running a very advanced query with a lot of sub-queries. In the book it does not explain the sub-queries in detail. It only shows the query and gives a result set. I am posting the query below so it can be seen. When I run it I am not getting the same results. First, I am not sure of the order of evaluation of each of the sub-queries (i.e. which get run first) and second I don't quite understand the table aliasing being used in the query. If some one could help me understand what is exactally going on, or point me in the right direction that would be great! Query below:
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate
FROM Orders AS o1
WHERE OrderDate = (SELECT MAX(OrderDate) AS Expr1
FROM Orders AS o2
WHERE (EmployeeID = o1.EmployeeID) AND
(RequiredDate = (SELECT MAX(RequiredDate) AS Expr1 FROM Orders AS o2
WHERE EmployeeID = o1.EmployeeID) AND (OrderDate = o1.OrderDate)
AND (SELECT MAX(OrderID) AS Expr1 FROM Orders AS o2
WHERE (EmployeeID = o1.EmployeeID) AND ((OrderDate = o1.OrderDate)))))))
I am not saying anything bad about the book but I am just learning advanced querying and someone who is an expert providing a little assistance would be great.
Thanks,
Steve
|
|
|
|
|
|
Is this what you were trying to do?
SELECT o1.OrderID, o1.CustomerID, o1.EmployeeID, o1.OrderDate, o1.RequiredDate
FROM Orders AS o1
WHERE o1.OrderDate = (SELECT MAX(o2.OrderDate)
FROM Orders AS o2
WHERE o2.EmployeeID = o1.EmployeeID)
AND o1.RequiredDate = (SELECT MAX(o3.RequiredDate)
FROM Orders AS o3
WHERE o3.EmployeeID = o1.EmployeeID
AND o3.OrderDate = o1.OrderDate)
AND o1.OrderId = (SELECT MAX(o4.OrderID)
FROM Orders AS o4
WHERE o4.EmployeeID = o1.EmployeeID
AND o4.OrderDate = o1.OrderDate)
I find it much easier to read if I indent the code and explicitly use table alias'. The intent of the sql becomes much clearer.
|
|
|
|
|
Your ordering has made it much more clear. Thanks for that! One last question. Do I read the subqueries from top to bottom or bottom to top. What is the order of execution?
Much thanks,
Steve Holdorf
|
|
|
|
|
There is no "order" invoked by the positioning of the subqueries. SQL will build an execution plan (you REALLY need to study and understand these to get some performance enhancements) that is based on the cost of each query.
I have a base rule that says I use the strongest (returns the least row) filter first but I doubt it makes a difference.
|
|
|
|
|
I use the same approach because it helps clarify my intent but as you say it actually doesn't have any impact upon the query. The Sql Server Query Optimizer will determine the most efficient route based on the latest statistics.
|
|
|
|
|
Below is a multi inner join query that works. The problem is that I don't understand how it is evaluated and need help understanding what it is doing. Can someone explain in detail what the query is doing because I don't quite understand?
SELECT DISTINCT o.OrderDate, c.CompanyName, od.Quantity, p.ProductName
FROM Customers AS c INNER JOIN
Orders AS o ON o.CustomerID = c.CustomerID INNER JOIN
[Order Details] AS od ON od.OrderID = o.OrderID INNER JOIN
Products AS p ON p.ProductID = od.ProductID
Thanks,
Steve Holdorf
|
|
|
|
|
There used to be a good article on CP explaining join types but I couldn't find it, this returns[^] what should help
Basically an inner join returns records that have the same values in the tables on each side of the join.
Orderdetail => product There will be a foriegn key constraint on the OD table that says an orderid cannot be entered unless there is a product record, therefore this join will no do any filtering but will be used as a lookup to get the productname for each orderdetail.
Order => Orderdetail This gets the detail records for each order record and will be a 1 to many join.
Customer => Order is the same as order/detail join
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft is referring to this[^] article.
I are Troll 
|
|
|
|
|
Thanks Eddy, that is exactly the article I was after, have to bookmark it for future reference.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You're welcome
|
|
|
|
|
|
There are two databases in different locations.one in my local database server and the other in a remote server. I want to synchronize theses two databases so that the data available in both servers are exactly the same .when ever any dml operation takes place on one server then the other should also be replicated .What are the bottlenecks and the step by step procedure of implementation.
|
|
|
|
|
try Googling and what problem are you facing bcz its not that much small topic that can be answered in a post.
Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it
|
|
|
|
|
That is a very large topic, you are not going to get an sensible answer in a forum post, as softie said try Google [^]as the first step in your research.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm having a tree structure using the Adjacency list model, having an ID and ParentID for every node in the tree.
I need to find the lowest common ancestor for two or more IDs.
Anyone that knows how to do this?
(I know how to do it with the path enumeration or nested set model, but that doesn't help me here)
|
|
|
|
|
Jörgen Andersson wrote: using the Adjacency list model
I'm curious, what is this?
What database are you using?
Does this model dictate the format of your keys?
I use the hierarchyid in SQL Server to store my structures. How you would achieve this is dependant on your data structure.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Adjacency_list[^] is what Joe Celko calls what probably is the most common representation of a tree in a database.
Where every node has an ID and also stores the id of its parent.
I'm using Oracle
|
|
|
|
|
Thank you, I'm sorry not to be able to help, I have not used Oracle in a decade or so.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I want to update the top row in a table. I'm trying to get this to work:
update top (1) mytable set myfield="test"
This does not seem to compile. We have Microsoft SQL Server 2000. How can I accomplish this?
|
|
|
|
|
AFAIK, the update statement does not support the TOP condition. Try something like this;
UPDATE dbo.myTable
SET myField = 'test'
WHERE myPrimaryKeyField = (SELECT TOP 1 myPrimaryKeyField FROM dbo.myTable) Hope this helps
I are Troll
|
|
|
|
|
You should have an ORDER BY clause on the subquery to determine the correct TOP 1.
|
|
|
|
|
A more efficient statement would be this.
update Table
set field = new_value
where PK = (select min(PK) from Table)
only two letters away from being an asset
|
|
|
|
|
The predicate "TOP 1" applies only to the SELECT statement, not the UPDATE statement.
In order to update just the first row in your table, your UPDATE statement will need to be
UPDATE mytable
SET myfield = "test"
WHERE ((SELECT TOP 1 myfield FROM mytable) = myfield);
(I've split the statement over several lines to improve readability).
|
|
|
|
|
Is there a way to do this:
@FirstId uniqueidentifier = NULL;
@SecondId uniqueidentifier = NULL;
SELECT ..
FROM ..
WHERE
IF(@FirstId IS NOT NULL)
BEGIN
FirstId = @FirstId
END
IF(@SecondId IS NOT NULL)
BEGIN
AND FirstId = @FirstId
END
Or a smarter way? 
Thanks in advance!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
