November 5, 2007 at 5:34 am
b_boy (11/5/2007)
Hello RameshThat was what i was trying to do, for quite some time, can you kindly explain to me how you created that query, as I can see that you have a sub-query within a join statement?
I need to understand how you derived your query
...The below query is not a sub-query. A sub-query is a query which is evaluated for each and every row in the outer query. This type of query is termed as a derived table.
..The optimizer first evaluates the results of the inner query (the derived table) before joining to the key columns.
SELECTbk_no, COUNT( * ) AS Orders
FROMordered_items
GROUP BY bk_no
--Ramesh
November 5, 2007 at 8:38 am
Well actually Ramesh - a derived table IS a sub-query, just not one that is "re-run" for each record in the main table. That's what's called a Correlated sub-query.
And yes - CORRELATED sub-queries are evil and should be tracked down and shot when at all possible.:P
----------------------------------------------------------------------------------
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?
November 5, 2007 at 8:03 pm
Heh... that's why I sometimes hate BOL... here's the definition of "subquery" according to BOL which, incidently, agrees with Ramesh's definition...
A subquery is a SELECT query that returns a single value and is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed. In this example a subquery is used as a column expression named MaxUnitPrice in a SELECT statement.
SELECT Ord.OrderID, Ord.OrderDate,
(SELECT MAX(OrdDet.UnitPrice)
FROM Northwind.dbo.[Order Details] AS OrdDet
WHERE Ord.OrderID = OrdDet.OrderID) AS MaxUnitPrice
FROM Northwind.dbo.Orders AS Ord
...of course, if you start to hone in on the words "correlated subquery", you get this...
Correlated Subqueries
Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query.
... which is what Matt is talking about but still doesn't necessarily contradict Ramesh's statement. But it does give a hint that there may be more than one type of subquery.
Not that I'm an expert on these types of things, but I agree with Matt's implied definition just because it's easy to remember and talk about... any time a SELECT appears anywhere within another SELECT, I call it a "subquery" and I will usually make the difference between two types of "subqueries" as follows (just like Matt)...
A subquery that returns just one value, is executed once for each row in the outer query (RBAR on steriods), and makes required reference to the outer query is a "correlated subquery" to me.
A subquery that returns one or more values as a result set that may be used as if from a table or view in a FROM clause is what I refer to as either a "derived table" or an "inline view".
CTE's, to me, are nothing more than another form of subquery that acts as a "derived table" or "inline view".
But, I've got nothing from BOL to support those decisions or definitions because it looks like BOL says a subquery "returns a single value". I do, however, have some consensus starting with Matt and ending with multiple searches such as the following...
http://www.xaprb.com/blog/2005/09/26/sql-subqueries-and-derived-tables/
According to that wonderful bit of science, all "simple" SELECTs can be interpreted as "derived tables" :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2007 at 9:31 pm
Jeff Moden, A correlated subquery is a SELECT statement nested inside another T-SQL statement, which contains a reference to one or more columns in the outer query. The correlated subquery will be run once for each candidate row selected by the outer query.
select A.*
from table1 A
where A.ID in ( select id From table2 B where A.ID = B.ID )
When this query is executed, the SQL server will execute the inner query, the correlated subquery, for each table1 ID. This inner query will fetch the id for the particular id from table1 (the candidate row being processed in the outer query). This correlated subquery determines if the inner query returns a value that meets the condition of the where clause.
November 5, 2007 at 11:25 pm
Heh... I appreciate the feedback and, if you look at my post, I agree... I was trying to point out that Books Online leaves a bit to be desired in it's definitions in places because, according to one of it's definitions, all subqueries are scalar in nature... we all know that to NOT be true. But, it does lend to Ramesh's statement being true if you believe in Books Online...;)
--Jeff Moden
Change is inevitable... Change for the better is not.
November 6, 2007 at 12:10 am
...I do agree with what Matt & Jeff said.
--Ramesh
November 6, 2007 at 12:50 am
wow.......i just want thank you all for the coaching,it has been really helpful.:)
November 6, 2007 at 12:56 am
Or a SQL Server 2005 solution?
select*
from(
SELECTbooks.*,
count(*) over (partition by books.bk_no) AS items
FROMbooks
INNER JOINordered_items ON ordered_items.bk_no = books.bk_no
) as d
whereitems > 1
N 56°04'39.16"
E 12°55'05.25"
November 23, 2007 at 6:42 pm
SELECT books.bk_no
FROM books
INNER JOIN ordered_items
ON books.bk_no = ordered_items.bk_no
GROUP BY books.bk_no
HAVING COUNT(ordered_items.bk_no)>2
This is beause you haven't included the'books.title' in your group by
What you can do is
select * from books
where book.bk_no in (
SELECT books.bk_no
FROM books
INNER JOIN ordered_items
ON books.bk_no = ordered_items.bk_no
GROUP BY books.bk_no
HAVING COUNT(ordered_items.bk_no)>2)
You should get what you want.
b_boy (11/4/2007)
having difficulty trying to retrieve the rest of the details, am using :SELECT books.*
FROM books
INNER JOIN ordered_items
ON books.bk_no = ordered_items.bk_no
GROUP BY books.bk_no
HAVING COUNT(ordered_items.bk_no)>2
and getting the error message:
Msg 8120, Level 16, State 1, Line 1
Column 'books.title' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Also when you say "To get the rest of the details, you will need to join back to the books table." what do you mean?
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply