Conditional Table Joins

Hi all,
I'm having a little trouble with producing a query for MS SQL Server
2000 with a conditional join in.
The basis of the structure is that when an order is active, the
OrderID of any book in the Order is stored in the book record in the
Books table. If an order is cancelled, the OrderID is removed from the
record in the Books table, to enable the book to be put back on sale.
At that point, for each book cancelled, a record is appended to the
CancelledRefundedBooks table, with just the book reference number
(foreign key of the Books table), and the OrderID it belonged to. This
is to us to still see which books were in a cancelled order. I hope
that made sense! i've posted the table defs for these tables at the
bottom of this message.
My problem is when using the following stored procedure query to
search for orders based on different criteria, included a reference
number for a book in the order, the join with the books table is
causing cancelled orders not to be returned. For example, if i search
for active order 1, because one of the possible search criteria is
book reference, the Books table is joined to the query. Because order
1 is active, the book records in the Books table have orderID's
stored, and the order is returned. However, if i search for cancelled
order 2, because the books are lacking the OrderID field, the join is
causing the order record not to be returned. I have tried using an
INNER JOIN and a LEFT OUTER JOIN, and neither help. The ideal
situation would be if the join for the Books table could be
conditional on the OrderStatusID field in the Orders table, so that
the query would be joined to the Books table if the order was active,
and if the order was cancelled, the join would change to the
CancelledRefundedBooks table.
An additional complication is that if the join was to the
CancelledRefundedBooks table, in the event of a cancelled order, the
CancelledRefundedBooks table holds only the book reference numbers.
This means that table would then need a further join onto the Books
table to retrieve author, title etc. fields.
The stored proc as it stands is as follows:
ALTER PROCEDURE sp_searchorders
@.refnumber int = NULL,
@.surname nvarchar(100) = NULL,
@.orderid int = NULL,
@.postcode nvarchar(50) = NULL,
@.booktitle nvarchar(500) = NULL
WITH RECOMPILE
AS
SELECT DISTINCT
Orders.OrderID, Staff.Surname AS StaffSur,
Staff.FirstName AS StaffFir, Orders.CustomerID, Customers.Forenames,
Customers.Surname,
Customers.Telephone, Customers.Email,
Customers.Forenames, Customers.Surname, Customers.TownCity,
Websites.Name AS Website, Orders.OrdDate
FROM Orders INNER JOIN
Customers ON Orders.CustomerID = Customers.CustomerID INNER JOIN
Staff ON Orders.StaffID = Staff.StaffID INNER
JOIN
Websites ON Orders.WebsiteID = Websites.WebsiteID LEFT OUTER JOIN
Books ON Orders.OrderID = Books.OrderID
WHERE CASE @.refnumber
WHEN 0 THEN @.refnumber
ELSE Books.Ref
END
= @.refnumber
AND Customers.Surname LIKE COALESCE(@.surname, '%')
AND Books.Title LIKE COALESCE(@.booktitle, '%')
AND Customers.Postcode LIKE COALESCE(@.postcode, '%')
AND CASE @.orderid
WHEN 0 THEN @.orderid
ELSE Orders.OrderID
END
= @.orderid
My sincerest apologies for posting such a long narrative about the
problem, but I can't think of any other way of describing it! I have
included the CREATE statements for all the mentioned tables below, and
if i can provide any more information to help come up with a solution
plz ask. I am a relative newbie at SQL server, so please be gentle!!
Many thanks in advance
James Currer
CREATE TABLE [Books] (
[Ref] [int] NOT NULL ,
[Author] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
[Title] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[PlacePubDate] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[Description] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[Keywords] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
[Catalogues] [nvarchar] (80) COLLATE Latin1_General_CI_AS NULL ,
[Cost] [money] NULL ,
[DealerCode] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Notes] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[Price] [money] NULL ,
[OrderID] [int] NULL ,
[Weight] [numeric](6, 3) NULL ,
[ISBN] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Row] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Shelf] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[LocationID] [int] NULL ,
[StatusID] [int] NULL ,
[DealerID] [int] NULL ,
[BoxID] [int] NULL ,
[LastUpdatedBy] [int] NULL ,
CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED
(
[Ref]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [CancelledRefundedBooks] (
[BookRef] [int] NOT NULL ,
[RefundID] [int] NULL ,
[OrderID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [Orders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[StaffID] [int] NOT NULL ,
[CustomerID] [int] NOT NULL ,
[OrdDate] [datetime] NULL ,
[ShipVia] [int] NULL ,
[WebsiteID] [int] NOT NULL ,
[PackingID] [int] NULL ,
[PaymentID] [int] NULL ,
[InvoiceNumber] [int] NULL ,
[InvoiceDate] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[OrderStatusID] [int] NOT NULL CONSTRAINT [DF_Orders_OrderStatusID]
DEFAULT (4),
[TotalPricePaid] [money] NOT NULL CONSTRAINT
[DF_Orders_TotalPricePaid] DEFAULT (0),
[CustomersOwnRef] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[ShippingPaid] [money] NOT NULL CONSTRAINT [DF_Orders_ShippingPaid]
DEFAULT (0.00),
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
) ON [PRIMARY]
GOJames,
I did not go beyond your second paragraph. I believe your db design is
flawed. If you keep an OrderID in your Book table for an active order, how
do you deal with the situation that there more than one orders that buy the
same book?
"James Currer" <phaser2001@.hotmail.com> wrote in message
news:57df26d.0307300626.667496bf@.posting.google.com...
> Hi all,
> I'm having a little trouble with producing a query for MS SQL Server
> 2000 with a conditional join in.
> The basis of the structure is that when an order is active, the
> OrderID of any book in the Order is stored in the book record in the
> Books table. If an order is cancelled, the OrderID is removed from the
> record in the Books table, to enable the book to be put back on sale.
> At that point, for each book cancelled, a record is appended to the
> CancelledRefundedBooks table, with just the book reference number
> (foreign key of the Books table), and the OrderID it belonged to. This
> is to us to still see which books were in a cancelled order. I hope
> that made sense! i've posted the table defs for these tables at the
> bottom of this message.
> My problem is when using the following stored procedure query to
> search for orders based on different criteria, included a reference
> number for a book in the order, the join with the books table is
> causing cancelled orders not to be returned. For example, if i search
> for active order 1, because one of the possible search criteria is
> book reference, the Books table is joined to the query. Because order
> 1 is active, the book records in the Books table have orderID's
> stored, and the order is returned. However, if i search for cancelled
> order 2, because the books are lacking the OrderID field, the join is
> causing the order record not to be returned. I have tried using an
> INNER JOIN and a LEFT OUTER JOIN, and neither help. The ideal
> situation would be if the join for the Books table could be
> conditional on the OrderStatusID field in the Orders table, so that
> the query would be joined to the Books table if the order was active,
> and if the order was cancelled, the join would change to the
> CancelledRefundedBooks table.
> An additional complication is that if the join was to the
> CancelledRefundedBooks table, in the event of a cancelled order, the
> CancelledRefundedBooks table holds only the book reference numbers.
> This means that table would then need a further join onto the Books
> table to retrieve author, title etc. fields.
> The stored proc as it stands is as follows:
> ALTER PROCEDURE sp_searchorders
> @.refnumber int = NULL,
> @.surname nvarchar(100) = NULL,
> @.orderid int = NULL,
> @.postcode nvarchar(50) = NULL,
> @.booktitle nvarchar(500) = NULL
> WITH RECOMPILE
> AS
> SELECT DISTINCT
> Orders.OrderID, Staff.Surname AS StaffSur,
> Staff.FirstName AS StaffFir, Orders.CustomerID, Customers.Forenames,
> Customers.Surname,
> Customers.Telephone, Customers.Email,
> Customers.Forenames, Customers.Surname, Customers.TownCity,
> Websites.Name AS Website, Orders.OrdDate
> FROM Orders INNER JOIN
> Customers ON Orders.CustomerID => Customers.CustomerID INNER JOIN
> Staff ON Orders.StaffID = Staff.StaffID INNER
> JOIN
> Websites ON Orders.WebsiteID => Websites.WebsiteID LEFT OUTER JOIN
> Books ON Orders.OrderID = Books.OrderID
> WHERE CASE @.refnumber
> WHEN 0 THEN @.refnumber
> ELSE Books.Ref
> END
> = @.refnumber
> AND Customers.Surname LIKE COALESCE(@.surname, '%')
> AND Books.Title LIKE COALESCE(@.booktitle, '%')
> AND Customers.Postcode LIKE COALESCE(@.postcode, '%')
> AND CASE @.orderid
> WHEN 0 THEN @.orderid
> ELSE Orders.OrderID
> END
> = @.orderid
> My sincerest apologies for posting such a long narrative about the
> problem, but I can't think of any other way of describing it! I have
> included the CREATE statements for all the mentioned tables below, and
> if i can provide any more information to help come up with a solution
> plz ask. I am a relative newbie at SQL server, so please be gentle!!
> Many thanks in advance
>
> James Currer
>
> CREATE TABLE [Books] (
> [Ref] [int] NOT NULL ,
> [Author] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
> [Title] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
> [PlacePubDate] [ntext] COLLATE Latin1_General_CI_AS NULL ,
> [Description] [ntext] COLLATE Latin1_General_CI_AS NULL ,
> [Keywords] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
> [Catalogues] [nvarchar] (80) COLLATE Latin1_General_CI_AS NULL ,
> [Cost] [money] NULL ,
> [DealerCode] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [Notes] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
> [Price] [money] NULL ,
> [OrderID] [int] NULL ,
> [Weight] [numeric](6, 3) NULL ,
> [ISBN] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [Row] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [Shelf] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [LocationID] [int] NULL ,
> [StatusID] [int] NULL ,
> [DealerID] [int] NULL ,
> [BoxID] [int] NULL ,
> [LastUpdatedBy] [int] NULL ,
> CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED
> (
> [Ref]
> ) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> CREATE TABLE [CancelledRefundedBooks] (
> [BookRef] [int] NOT NULL ,
> [RefundID] [int] NULL ,
> [OrderID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [Orders] (
> [OrderID] [int] IDENTITY (1, 1) NOT NULL ,
> [StaffID] [int] NOT NULL ,
> [CustomerID] [int] NOT NULL ,
> [OrdDate] [datetime] NULL ,
> [ShipVia] [int] NULL ,
> [WebsiteID] [int] NOT NULL ,
> [PackingID] [int] NULL ,
> [PaymentID] [int] NULL ,
> [InvoiceNumber] [int] NULL ,
> [InvoiceDate] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [OrderStatusID] [int] NOT NULL CONSTRAINT [DF_Orders_OrderStatusID]
> DEFAULT (4),
> [TotalPricePaid] [money] NOT NULL CONSTRAINT
> [DF_Orders_TotalPricePaid] DEFAULT (0),
> [CustomersOwnRef] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [ShippingPaid] [money] NOT NULL CONSTRAINT [DF_Orders_ShippingPaid]
> DEFAULT (0.00),
> CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
> (
> [OrderID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO