Query Help

Hi All,
I'm not sure if this even possible.
Here is the query from NorthWind database:
SELECT TOP 3 Employees.EmployeeID,
Orders.CustomerID
FROM Orders,Employees
WHERE Employees.EmployeeID = 9
AND Orders.EmployeeID = Employees.EmployeeID
Result:
EmployeeID CustomerID
9 RICSU
9 ERNSH
9 SAVEA
Is there a way to return this query as 1 record and name columns
CustomerID1, CustomerID2, CustomerID3
EmployeeID CustomerID1 CustomerID2
CustomerID3
9 RICSU ERNSH
SAVEA
Thanks,
Walter
I'd say do that in your user interface code rather than in SQL. Leave
the database for what it does best - retrieving data. How difficult can
it be to display a data set as horizontal list?
Certainly you can do it in the database, but do you really want 3
*random* customers for each employee? You haven't specified ORDER BY in
your query so you can't predict which three customers you'll get back.
Let's assume that you really wanted the first three orders placed by an
employee:
SELECT MAX(employeeid),
MAX(CASE WHEN ord = 1 THEN customerid END),
MAX(CASE WHEN ord = 2 THEN customerid END),
MAX(CASE WHEN ord = 3 THEN customerid END)
FROM
(SELECT O1.employeeid, O1.customerid, COUNT(*) AS ord
FROM Orders AS O1
JOIN Orders AS O2
ON O1.employeeid = O2.employeeid
AND (O1.orderdate > O2.orderdate OR
(O1.orderdate = O2.orderdate AND O1.orderid >= O2.orderid))
WHERE O1.employeeid =9
GROUP BY O1.employeeid, O1.customerid, O1.orderdate
HAVING COUNT(*)<=3) AS T ;
David Portas
SQL Server MVP
|||David,
Thanks for your help.Works like a champ!
I don't have an interface for this query.
Walter
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124208616.358495.95330@.g47g2000cwa.googlegro ups.com...
> I'd say do that in your user interface code rather than in SQL. Leave
> the database for what it does best - retrieving data. How difficult can
> it be to display a data set as horizontal list?
> Certainly you can do it in the database, but do you really want 3
> *random* customers for each employee? You haven't specified ORDER BY in
> your query so you can't predict which three customers you'll get back.
> Let's assume that you really wanted the first three orders placed by an
> employee:
> SELECT MAX(employeeid),
> MAX(CASE WHEN ord = 1 THEN customerid END),
> MAX(CASE WHEN ord = 2 THEN customerid END),
> MAX(CASE WHEN ord = 3 THEN customerid END)
> FROM
> (SELECT O1.employeeid, O1.customerid, COUNT(*) AS ord
> FROM Orders AS O1
> JOIN Orders AS O2
> ON O1.employeeid = O2.employeeid
> AND (O1.orderdate > O2.orderdate OR
> (O1.orderdate = O2.orderdate AND O1.orderid >= O2.orderid))
> WHERE O1.employeeid =9
> GROUP BY O1.employeeid, O1.customerid, O1.orderdate
> HAVING COUNT(*)<=3) AS T ;
> --
> David Portas
> SQL Server MVP
> --
>

Query Help

Hi All,
I'm not sure if this even possible.
Here is the query from NorthWind database:
SELECT TOP 3 Employees.EmployeeID,
Orders.CustomerID
FROM Orders,Employees
WHERE Employees.EmployeeID = 9
AND Orders.EmployeeID = Employees.EmployeeID
Result:
EmployeeID CustomerID
9 RICSU
9 ERNSH
9 SAVEA
Is there a way to return this query as 1 record and name columns
CustomerID1, CustomerID2, CustomerID3
EmployeeID CustomerID1 CustomerID2
CustomerID3
9 RICSU ERNSH
SAVEA
Thanks,
WalterI'd say do that in your user interface code rather than in SQL. Leave
the database for what it does best - retrieving data. How difficult can
it be to display a data set as horizontal list?
Certainly you can do it in the database, but do you really want 3
*random* customers for each employee? You haven't specified ORDER BY in
your query so you can't predict which three customers you'll get back.
Let's assume that you really wanted the first three orders placed by an
employee:
SELECT MAX(employeeid),
MAX(CASE WHEN ord = 1 THEN customerid END),
MAX(CASE WHEN ord = 2 THEN customerid END),
MAX(CASE WHEN ord = 3 THEN customerid END)
FROM
(SELECT O1.employeeid, O1.customerid, COUNT(*) AS ord
FROM Orders AS O1
JOIN Orders AS O2
ON O1.employeeid = O2.employeeid
AND (O1.orderdate > O2.orderdate OR
(O1.orderdate = O2.orderdate AND O1.orderid >= O2.orderid))
WHERE O1.employeeid =9
GROUP BY O1.employeeid, O1.customerid, O1.orderdate
HAVING COUNT(*)<=3) AS T ;
David Portas
SQL Server MVP
--|||David,
Thanks for your help.Works like a champ!
I don't have an interface for this query.
Walter
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124208616.358495.95330@.g47g2000cwa.googlegroups.com...
> I'd say do that in your user interface code rather than in SQL. Leave
> the database for what it does best - retrieving data. How difficult can
> it be to display a data set as horizontal list?
> Certainly you can do it in the database, but do you really want 3
> *random* customers for each employee? You haven't specified ORDER BY in
> your query so you can't predict which three customers you'll get back.
> Let's assume that you really wanted the first three orders placed by an
> employee:
> SELECT MAX(employeeid),
> MAX(CASE WHEN ord = 1 THEN customerid END),
> MAX(CASE WHEN ord = 2 THEN customerid END),
> MAX(CASE WHEN ord = 3 THEN customerid END)
> FROM
> (SELECT O1.employeeid, O1.customerid, COUNT(*) AS ord
> FROM Orders AS O1
> JOIN Orders AS O2
> ON O1.employeeid = O2.employeeid
> AND (O1.orderdate > O2.orderdate OR
> (O1.orderdate = O2.orderdate AND O1.orderid >= O2.orderid))
> WHERE O1.employeeid =9
> GROUP BY O1.employeeid, O1.customerid, O1.orderdate
> HAVING COUNT(*)<=3) AS T ;
> --
> David Portas
> SQL Server MVP
> --
>

Query Help

Hi All,
I'm not sure if this even possible.
Here is the query from NorthWind database:
SELECT TOP 3 Employees.EmployeeID,
Orders.CustomerID
FROM Orders,Employees
WHERE Employees.EmployeeID = 9
AND Orders.EmployeeID = Employees.EmployeeID
Result:
EmployeeID CustomerID
9 RICSU
9 ERNSH
9 SAVEA
Is there a way to return this query as 1 record and name columns
CustomerID1, CustomerID2, CustomerID3
EmployeeID CustomerID1 CustomerID2
CustomerID3
9 RICSU ERNSH
SAVEA
Thanks,
WalterI'd say do that in your user interface code rather than in SQL. Leave
the database for what it does best - retrieving data. How difficult can
it be to display a data set as horizontal list?
Certainly you can do it in the database, but do you really want 3
*random* customers for each employee? You haven't specified ORDER BY in
your query so you can't predict which three customers you'll get back.
Let's assume that you really wanted the first three orders placed by an
employee:
SELECT MAX(employeeid),
MAX(CASE WHEN ord = 1 THEN customerid END),
MAX(CASE WHEN ord = 2 THEN customerid END),
MAX(CASE WHEN ord = 3 THEN customerid END)
FROM
(SELECT O1.employeeid, O1.customerid, COUNT(*) AS ord
FROM Orders AS O1
JOIN Orders AS O2
ON O1.employeeid = O2.employeeid
AND (O1.orderdate > O2.orderdate OR
(O1.orderdate = O2.orderdate AND O1.orderid >= O2.orderid))
WHERE O1.employeeid =9
GROUP BY O1.employeeid, O1.customerid, O1.orderdate
HAVING COUNT(*)<=3) AS T ;
--
David Portas
SQL Server MVP
--|||David,
Thanks for your help.Works like a champ!
I don't have an interface for this query.
Walter
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124208616.358495.95330@.g47g2000cwa.googlegroups.com...
> I'd say do that in your user interface code rather than in SQL. Leave
> the database for what it does best - retrieving data. How difficult can
> it be to display a data set as horizontal list?
> Certainly you can do it in the database, but do you really want 3
> *random* customers for each employee? You haven't specified ORDER BY in
> your query so you can't predict which three customers you'll get back.
> Let's assume that you really wanted the first three orders placed by an
> employee:
> SELECT MAX(employeeid),
> MAX(CASE WHEN ord = 1 THEN customerid END),
> MAX(CASE WHEN ord = 2 THEN customerid END),
> MAX(CASE WHEN ord = 3 THEN customerid END)
> FROM
> (SELECT O1.employeeid, O1.customerid, COUNT(*) AS ord
> FROM Orders AS O1
> JOIN Orders AS O2
> ON O1.employeeid = O2.employeeid
> AND (O1.orderdate > O2.orderdate OR
> (O1.orderdate = O2.orderdate AND O1.orderid >= O2.orderid))
> WHERE O1.employeeid =9
> GROUP BY O1.employeeid, O1.customerid, O1.orderdate
> HAVING COUNT(*)<=3) AS T ;
> --
> David Portas
> SQL Server MVP
> --
>

Query example

Hi,

I have to select two different column values from two different tables based on one condition.

Like, I have two tables - orders and fulfillment. I have to select orders.order_id and fulfillment.fulfillment_id for product_id = 2 and date_added = '08/18/06'. The only common fields between the two tables are customer_id,order_id,product_id,date_added.

I tried doing this but I know that won't work because it is returning all the rows from orders and fulfillment.

select o.order_id ,f.fulfillment_id

from orders o,fulfillment f

where o.product_id = 2

and o.date_added = '08/18/06'

and f.product_id = 2

and f.date_added = '08/18/2006'

Can somebody please suggest me how to select records from these two tables based on thte above condition?

Thanks,

Reva,

The details about the relationships of the data between the two tables are a little vague, but let me give it a try:

SELECT
o.order_id,
f.fulfillment_ID
FROM orders AS o
JOIN fulfillment AS f
ON o.order_id = f.order_id
WHERE o.product_id = 2
AND o.date_added = '08/18/06'

You will note that I don't have criteria in the WHERE clause of the f.date_added and f.product_id fields. I am assuming that the date_added and product_id values for a fulfillment are the same as the date_added and product_id values for the related order. If that is the case, you only need to filter the values in one of the tables.

The previous query uses the JOIN and ON clauses to state the join criteria. This is the standars compliant syntax that makes JOINS more accurate and less likely to cause unexpected results.

The problem with your query is that you used the older "legacy" style join syntax of FROM orders,fulfillment. When you do that you MUST state the join criteria (o.order_id = f.order_id) for the rows to match. Otherwise you get what is called a "CROSS JOIN" where every row of the first table is joined to every row of the second table. That is why you are seeing all the extra rows you didn't want. The syntax I used above prevents that from happening by accident because the ON clause is REQUIRED unless CROSS JOIN is explicitly stated.

I hope that helps.

Query cost

In many cases, I am finding that SQL Server 2005 generates the same plan as SQL Server 2000, but the SQL 2005 costs are orders of magnitude greater than the SQL 2000. Also, the execution time and cpu time shown by SET STATISTICS IO/TIME on are greater in 2005 than they are in 2000. Are the costs not comparable across the two versions?

Sharon

Sharon,

The cost calculation is different in SQL Server 2005.
Cost is now calculated by the number of ticks (based on memory, io and context switch cost). So that may explain the cost difference.

I'll try to see if I'm getting the same behaviour with statistics io and time.

|||

Thanks, Wesley.

I see different patterns in statistics IO and I see significant increases in CPU and elapsed time for SQL Server 2005 vs. SQL Server 2000.

Can you point me to any documentation about a changed costing algorithm?

Sharon

|||

Sharon,

That is very odd indeed.
I'm going to try to find some time today because things like this get my attention :-)

This is a great article that holds some information about the cost calculation.

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

hth

Query Concept

Hi,
I would like to use Northwind as an example.
I am trying to use the Orders table and why the following query only returns
one record for each customer if the outer query pass info into the inner
query since each customer has more than one records in the Orders table.
e.g. customerid "ALFKI" has six records in Orders table, but after the query
run, it only returns one record for "ALFKI". I assume "ALFKI" should pass
into the inner query six times... it is supposed to show six identitcal
reocrds for "ALFKI", but not, why?
select a.* from orders a where a.orderdate = (select max(orderdate) from
orders b where a.customerid = b.customerid)
order by a.customerid
Thanks
EdmundIn principle the subquery is evaluated for each row but it will always
return the same value for any given customerid. If there is only one
value that corresponds to the maximum date for "ALKFI" then you will
only get one row returned for that customer.
Please show us exactly what result you want then maybe someone can help
with a solution.
David Portas
SQL Server MVP
--|||Actually, i am just wondering why I don't get six records back for customer
"ALFKI" but just one since I guess the outer query is passing into the inner
query for six times even I may get six identical records...
"David Portas" wrote:

> In principle the subquery is evaluated for each row but it will always
> return the same value for any given customerid. If there is only one
> value that corresponds to the maximum date for "ALKFI" then you will
> only get one row returned for that customer.
> Please show us exactly what result you want then maybe someone can help
> with a solution.
> --
> David Portas
> SQL Server MVP
> --
>|||Because the subquery will return only one record, which has the last
orderdate, for each CustomerID.
Perayu
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:D85ECABE-E0C5-431F-9948-B329086B1A31@.microsoft.com...
> Hi,
> I would like to use Northwind as an example.
> I am trying to use the Orders table and why the following query only
> returns
> one record for each customer if the outer query pass info into the inner
> query since each customer has more than one records in the Orders table.
> e.g. customerid "ALFKI" has six records in Orders table, but after the
> query
> run, it only returns one record for "ALFKI". I assume "ALFKI" should pass
> into the inner query six times... it is supposed to show six identitcal
> reocrds for "ALFKI", but not, why?
> select a.* from orders a where a.orderdate = (select max(orderdate) from
> orders b where a.customerid = b.customerid)
> order by a.customerid
> Thanks
> Edmund
>
>
>
>|||Maybe the following example will make it clearer:
SELECT a.customerid, a.orderdate,
(SELECT MAX(orderdate)
FROM orders b
WHERE a.customerid = b.customerid) AS subquery_result
FROM orders a
WHERE a.customerid = 'ALFKI' ;
Here I've just moved the subquery into the select list so that you can
see the result it returns for each row - the same value each time. You
can also see that there is only ONE row that matches the value returned
by the subquery, therefore that's the only row that satisfies the
condition in your WHERE clause.
David Portas
SQL Server MVP
--|||Ed,

> Actually, i am just wondering why I don't get six records back for custome
r
> "ALFKI" but just one since I guess the outer query is passing into the inn
er
> query for six times even I may get six identical records...
From all alfki's orders, just one is the latest. Eventhough the outer query
is passing value six times, the value is the same (customerid) and the value
returned is always the same (max(orderdate)).
declare @.d datetime
select @.d = max(orderdate)
from orders
where customerid = 'alfki'
print @.d
select *
from orders
where customerid = 'alfki' and orderdate = @.d
go
AMB
"Ed" wrote:
> Actually, i am just wondering why I don't get six records back for custome
r
> "ALFKI" but just one since I guess the outer query is passing into the inn
er
> query for six times even I may get six identical records...
> "David Portas" wrote:
>