April 17, 2008 at 2:42 pm
I have the following two tables scenario.
Table Name: Order Master
Cols: Order ID,Cust Name,Cust Address,Order Date
Date: 1, ABC, Boston, 01/01/2008
2, XYZ, London, 02/02/2008
3, JKL, Dublin, 02/12/2008
Table Name: Order Details
Cols: OrderItemID,OrderID,ItemName,ItemGroup,ItemPrice
Date: 1, 1, Piano, MusicItem,$500
2, 1, WBox, GeneralItem,$300
3, 1, Drums, MusicItem,$500
4, 3, Lamps, GeneralItem,$100
4, 3, Drums, MusicItem,$200
Condition 1: All Orders may or may not have Order Details.
Condition 2: All order details will have an Order ID reference from OrderMaster.
Trying to build a search list like this, with the default listing of all orders.
Search by
OrderID---------- ItemID----------
CustName---------- ItemName----------
-------------------------------------------
OrderID | CustName|TotalItems |OrderDate|
-------------------------------------------
1 ABC 3 01/01/2008
2 XYZ 0 02/02/2008
3 JKL 2 02/12/2008
If I specify my search on ItemName as Drums, I would get the result as
OrderID---------- ItemID----------
CustName---------- ItemName-Drums
-------------------------------------------
OrderID | CustName|TotalItems |OrderDate|
-------------------------------------------
1 ABC 3 01/01/2008
3 JKL 2 02/12/2008
Looking forward for help in building the query to achive this.
April 17, 2008 at 2:57 pm
I'd go with a derived table from the items table to get the quantity of items, then an "in sub-query" to get the orders.
For example:
select orderid, name, date, isnull(qty, 0) as qty
from dbo.orders
left outer join
(select orderid as qtyorderid, count(*) as qty
from dbo.orderitems
group by orderid) Qtys
on orders.orderid = qty.qtyorderid
where orderid in
(select orderid
from dbo.orderitems
where item = @search_parameter)
Something like that should do it. You'll need to modify it for an input parameter for the search term, and so on.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 17, 2008 at 3:10 pm
Tried the followin query based on your reply.
------------------
select orderid, custname, orderdate, isnull(qty, 0) as qty
from dbo.orders
left outer join
(select orderid as qtyorderid, count(*) as qty
from dbo.orderitems
group by orderid) Qtys
on orders.orderid = qty.qtyorderid
where orderid in
(select orderid
from dbo.orderitems
where itemname = 'drums')
-------------------
But I am getting the following error.
====================================
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "qty.qtyorderid" could not be bound.
==============================
April 17, 2008 at 3:14 pm
Sorry, there's a typo in the join. Should be "qtys.qtyorderid" (with an "s" at the end).
You'll also need to make sure it uses your actual table names and the actual column names. I wrote a generic query, you need to make sure it fits your tables.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 17, 2008 at 3:37 pm
That query worked, but only for search parameters having entries in the order item table.As per the query, the Join statement, would always looks for matching entries in the orderitems table.
I also need to get an order list, in which some of the orders may not have any order items. The desired result should be as follows.
OrderID---------- ItemID----------
CustName---------- ItemName----------
-------------------------------------------
OrderID | CustName|TotalItems |OrderDate|
-------------------------------------------
1 ABC 3 01/01/2008
2 XYZ 0 02/02/2008
3 JKL 2 02/12/2008
April 17, 2008 at 6:53 pm
Try this stored procedure, bit complicated though. If you pass itemname, it will return order details for that particular item, or else it will return for all orders.
Create Procedure Usp_OrderDetails
(@ItemName varchar(20))
As
Select M.OrderID,M.CustName,(select count(OrderItemID) from OrderDetails where OrderDetails.Orderid=M.orderid),OrderDate
From OrderMaster M INNER JOIN (
Select Case When @ItemName IS NULL Then OrderMaster.OrderID Else D.OrderID End OrderID
From OrderMaster LEFT OUTER JOIN
(Select Distinct OrderID From OrderDetails
Where ItemName=ISNULL(@ItemName,-999)) D
ON OrderMaster.OrderID = Case When @ItemName IS NULL Then OrderMaster.OrderID
Else D.OrderID End ) Orders on M.OrderID=Orders.OrderID
April 17, 2008 at 7:29 pm
This procedure works great.
But how can I include more parameters.
Does adding more parameters would make the query more complex.
Create Procedure Usp_OrderDetails
(@ItemName varchar(20)=null,
@ItemGroup varchar(20)=null,
@ItemPrice money = null
)
As
Select M.OrderID,M.CustName,
(select count(OrderItemID) from OrderDetails where
------------
----------
----------
Greatly appreciate your help on this.
April 17, 2008 at 10:18 pm
April 18, 2008 at 8:09 am
It would be "And". Assume if I have 10 searchable fields, and a search can be made on one field or a combination of fields.There is no gurantee, how many search parameters I would get in, and among those parameters, some may belong to the OrderMaster and some to OrderItems. If more than 1 Parameter is passed, I need to make an AND condition on those two parameters...
Create Procedure Usp_OrderDetails
(@ItemName varchar(20)=null,
@ItemGroup varchar(20)=null,
@ItemPrice money = null
@CustName varchar(20) = null,
@OrderID int = null
)
As
@SQL varchar(Max)
@SQL = "Select M.OrderID,M.CustName,
(select count(OrderItemID) from OrderDetails where ..."
------------
----------If I get a search paramter, I would append it as one of the where condition specifyingh the appropriate table to which the paramete value belongs to(as indicated below) -----------
--------------------
-----------
if (@ItemName != null)
set @SQL = @SQL + " orderitem.ItemName = @ItemName"
if (@ItemPrice != null)
set @SQL = @SQL + " orderitem.ItemPrice = @ItemPrice"
if (@CustName != null)
set @SQL = @SQL + " orderMaster.CustName = @CustName"
-----------
----------
EXEC sp_executesql @SQL
---------
April 18, 2008 at 8:12 am
You should change "if @ItemName != null" to "if @ItemName is not null". Unless you have ANSI NULLs turned off (which is usually a bad idea).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 18, 2008 at 8:39 am
Create Procedure Usp_OrderDetails
(@ItemName varchar(20))
As
Select M.OrderID,M.CustName,(select count(OrderItemID) from OrderDetails where OrderDetails.Orderid=M.orderid),OrderDate
From OrderMaster M INNER JOIN (
Select Case When @ItemName IS NULL Then OrderMaster.OrderID Else D.OrderID End OrderID
From OrderMaster LEFT OUTER JOIN
(Select Distinct OrderID From OrderDetails
Where ItemName=ISNULL(@ItemName,-999)) D
ON OrderMaster.OrderID = Case When @ItemName IS NULL Then OrderMaster.OrderID
Else D.OrderID End ) Orders on M.OrderID=Orders.OrderID
======================================
-This procedure works absolutely fine. But need help in adding additional search parameters. Assume if I have 10 searchable fields, and a search can be made on one field or a combination of fields.There is no gurantee, how many search parameters I would get in, and among those parameters, some may belong to the OrderMaster and some to OrderItems. Need help to handle these dynamic search conditions.
April 18, 2008 at 9:07 am
I have never been a fan of dynamic SQL when I do not have to. There is a solution to this as a SP that does not need dynamic SQL to run:
Given your tables:
Table Name: Order_Master
OrderID, CustName, CustAddress, OrderDate
1, ABC, Boston, 01/01/2008
2, XYZ, London, 02/02/2008
3, JKL, Dublin, 02/12/2008
Table Name: Order_Details
OrderItemID,OrderID,ItemName,ItemGroup, ItemPrice
1, 1, Piano, MusicItem, $500
2, 1, WBox, GeneralItem, $300
3, 1, Drums, MusicItem, $500
4, 3, Lamps, GeneralItem, $100
4, 3, Drums, MusicItem, $200
Here is a SP that find the info you a looking for:
CREATE PROCEDURE p_Get_OrderSummary (
@ItemNamevarchar(20)= null,
@ItemGroupvarchar(20)= null,
@ItemPricemoney= null,
@CustNamevarchar(20)= null,
@OrderIDint= null)
AS
/************************************************
Author:David Novak
Description:Returns a recordset of order summary info for various search items.
Parameters:
NameTypeDescription
@ItemNamevarchar(20)The ItemName to search for. If not supplied return all.
@ItemGroupvarchar(20)The ItemGroup to search for. If not supplied return all.
@ItemPricemoneyThe ItemPrice to search for. If not supplied return all.
@CustNamevarchar(20)The CustName to search for. If not supplied return all.
@OrderIDintThe OrderId to search for. If not supplied return all.
Returns:
OrderIdint
CustNamevarchar(20)
OrderDatedatetime
Qtyint
Revisions:
4/14/2008David NovakCreated
**************************************************/
SET NOCOUNT ON
SELECT orderid, custname, orderdate, ISNULL(qty, 0) AS qty
FROM Order_Master om
left join (SELECT orderid AS qtyorderid, COUNT(*) AS qty
FROM Order_Details
GROUP BY orderid) Qtys
on om.orderid = Qtys.qtyorderid
WHERE OrderId in (SELECT orderid FROM Order_Details WHERE itemname = ISNULL(@ItemName, ItemName))
AND OrderId IN (SELECT orderid FROM Order_Details WHERE ItemGroup = ISNULL(@ItemGroup, ItemGroup))
AND OrderId IN (SELECT orderid FROM Order_Details WHERE ItemPrice = ISNULL(@ItemPrice, ItemPrice))
AND CustName = ISNULL(@CustName, CustName)
AND OrderId = ISNULL(@OrderId, OrderId)
I hope this helps.
Dave Novak
April 18, 2008 at 9:30 am
In this particular case, the dynamic version will use indexes better than the other version, because of all the IsNull() operators. So, yes, the static version has some slight advantages in terms of ease to read, and so on, the other one will almost certainly perform better.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 18, 2008 at 9:33 am
DAVNovak (4/18/2008)
I have never been a fan of dynamic SQL when I do not have to. There is a solution to this as a SP that does not need dynamic SQL to run:Given your tables:
Table Name: Order_MasterOrderID, CustName, CustAddress, OrderDate
1, ABC, Boston, 01/01/2008
2, XYZ, London, 02/02/2008
3, JKL, Dublin, 02/12/2008
Table Name: Order_Details
OrderItemID,OrderID,ItemName,ItemGroup, ItemPrice
1, 1, Piano, MusicItem, $500
2, 1, WBox, GeneralItem, $300
3, 1, Drums, MusicItem, $500
4, 3, Lamps, GeneralItem, $100
4, 3, Drums, MusicItem, $200
Here is a SP that find the info you a looking for:
CREATE PROCEDURE p_Get_OrderSummary (@ItemNamevarchar(20)= null,
@ItemGroupvarchar(20)= null,
@ItemPricemoney= null,
@CustNamevarchar(20)= null,
@OrderIDint= null)
AS
/************************************************
Author:David Novak
Description:Returns a recordset of order summary info for various search items.
Parameters:
NameTypeDescription
@ItemNamevarchar(20)The ItemName to search for. If not supplied return all.
@ItemGroupvarchar(20)The ItemGroup to search for. If not supplied return all.
@ItemPricemoneyThe ItemPrice to search for. If not supplied return all.
@CustNamevarchar(20)The CustName to search for. If not supplied return all.
@OrderIDintThe OrderId to search for. If not supplied return all.
Returns:
OrderIdint
CustNamevarchar(20)
OrderDatedatetime
Qtyint
Revisions:
4/14/2008David NovakCreated
**************************************************/
SET NOCOUNT ON
SELECT orderid, custname, orderdate, ISNULL(qty, 0) AS qty
FROM Order_Master om
left join (SELECT orderid AS qtyorderid, COUNT(*) AS qty
FROM Order_Details
GROUP BY orderid) Qtys
on om.orderid = Qtys.qtyorderid
WHERE OrderId in (SELECT orderid FROM Order_Details WHERE itemname = ISNULL(@ItemName, ItemName))
AND OrderId IN (SELECT orderid FROM Order_Details WHERE ItemGroup = ISNULL(@ItemGroup, ItemGroup))
AND OrderId IN (SELECT orderid FROM Order_Details WHERE ItemPrice = ISNULL(@ItemPrice, ItemPrice))
AND CustName = ISNULL(@CustName, CustName)
AND OrderId = ISNULL(@OrderId, OrderId)
I hope this helps.
Dave Novak
To pick up on something GSquared was getting at - I would add OPTION (RECOMPILE) to this. Since you have such a latitude in your find, it would be dangerous (to your performance) to even bother keeping the exec plan. So - force the select statement to recompile each time.
----------------------------------------------------------------------------------
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?
April 18, 2008 at 9:34 am
This procedure works fine, as long the orderID have matching records in the orderdetails table.
Example 1:
exec p_Get_OrderSummary @ItemName = 'drums'
This gives the correct results.
OrderID | CustName |No.Items |Order Date
--------------------------------------
1ABC 3 2008-01-01
3JKL 2 2008-02-12
Example 2:
For ex: The order ID 2does not have any order details.
When I tried to execute this
exec p_Get_OrderSummary @OrderID = 2 or
exec p_Get_OrderSummary @CustName = 'XYZ'
I would get no results.
But the desired result would be
OrderID | CustName |No.Items |Order Date
--------------------------------------
2XYZ 02008-02-02
Example 3:
Also, another scenario is that, when no parameters are passed, It should return the following. It should return all the ordermaster records.
OrderID | CustName |No.Items |Order Date
--------------------------------------
1ABC 3 2008-01-01
2XYZ 0 2008-02-02
3JKL 2 2008-02-12
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply