Problem merging two queries

From: Tim Vernon (richard_at_mediahq.co.uk)
Date: 03/05/04 

Date: Fri, 5 Mar 2004 17:25:59 -0000

Hello all

I'm having some trouble with an aggregating query and was wondering if
anyone here could help me out. I have built 2 individual queries, but want
to combine these together (which is proving to be the difficult bit)

The queries purpose is to look at the OrderDetails tables, and the
PickingListDetails tables and work out what products are outstanding, and to
which customers (Registrants) and to which Address to send them to.

The first query I've done tells me which customers have Ordered which
products and which address they want them shipped to.

SELECT dbo.RegistrantsV2.RegistrantID, dbo.OrdersV2.DeliveryAddressID,
dbo.OrderDetails.StockID, SUM(dbo.OrderDetails.UpdatedQtyInOrder)
                      AS TotalOrdered
FROM dbo.RegistrantsV2 INNER JOIN
                      dbo.OrdersV2 ON dbo.RegistrantsV2.RegistrantID =
dbo.OrdersV2.RegistrantID INNER JOIN
                      dbo.OrderDetails ON dbo.OrdersV2.OrderID =
dbo.OrderDetails.OrderID
GROUP BY dbo.RegistrantsV2.RegistrantID, dbo.OrdersV2.DeliveryAddressID,
dbo.OrderDetails.StockID

The second tells me which products have been put onto Picking Lists and what
address they were sent to.

SELECT dbo.PickingLists.RegistrantID,
dbo.PickingLists.DeliveryAddressID, dbo.OrderDetails.StockID,
SUM(dbo.PickingListStockDetails.QtyOnPickingList)
                      AS QuantitySent
FROM dbo.PickingListStockDetails INNER JOIN
                      dbo.PickingLists ON
dbo.PickingListStockDetails.PickingListID = dbo.PickingLists.PickingListID
INNER JOIN
                      dbo.OrderDetails ON
dbo.PickingListStockDetails.OrderDetailID = dbo.OrderDetails.OrderDetailID
INNER JOIN
                      dbo.HattonsStock ON dbo.OrderDetails.StockID =
dbo.HattonsStock.StockID
GROUP BY dbo.PickingLists.RegistrantID, dbo.PickingLists.DeliveryAddressID,
dbo.OrderDetails.StockID

Now I want to combine these 2 into one View.

So the completed query would have 5 columns:

RegistrantID, DeliveryAddressID, StockID, TotalQuantityOrdered,
TotalQuantitySent.

It would be nice to have another column which had "TotalQuantityOutstanding"
which showed TotalQuantityOrder-TotalQuantitySent.

Could someone give any help for this please?

CREATE TABLE [RegistrantsV2] (
 [RegistrantID] [int] IDENTITY (1, 1) NOT NULL ,
 [Email] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [EmailConfirmed] [bit] NOT NULL CONSTRAINT
[DF_RegistrantsV2_EmailConfirmed] DEFAULT (0),
 [RegistrantPassword] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
 [PasswordReminder] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
 [MailingList] [bit] NOT NULL CONSTRAINT [DF_RegistrantsV2_MailingList]
DEFAULT (0),
 [Username] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 CONSTRAINT [PK_RegistrantsV2] PRIMARY KEY CLUSTERED
 (
  [RegistrantID]
 ) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [OrdersV2] (
 [OrderID] [int] IDENTITY (1, 1) NOT NULL ,
 [OrderStatusID] [int] NOT NULL CONSTRAINT [DF_OrdersV2_OrderStatusID]
DEFAULT (1),
 [RegistrantID] [int] NOT NULL ,
 [OrderSourceID] [int] NOT NULL ,
 [DeliveryAddressID] [int] NULL ,
 [CardholderAddressID] [int] NULL ,
 [ShippingMethodID] [int] NOT NULL CONSTRAINT [DF_OrdersV2_ShippingMethodID]
DEFAULT (1),
 [DeliveryNotes] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
 [PaymentNotes] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [TotalValueIncPostage] [float] NULL ,
 [OrderDate] [datetime] NOT NULL CONSTRAINT [DF_OrdersV2_OrderDate] DEFAULT
(getutcdate()),
 CONSTRAINT [PK_OrdersV2] PRIMARY KEY CLUSTERED
 (
  [OrderID]
 ) ON [PRIMARY] ,
 CONSTRAINT [FK_OrdersV2_AddressV2] FOREIGN KEY
 (
  [DeliveryAddressID]
 ) REFERENCES [AddressV2] (
  [AddressID]
 ),
 CONSTRAINT [FK_OrdersV2_AddressV21] FOREIGN KEY
 (
  [CardholderAddressID]
 ) REFERENCES [AddressV2] (
  [AddressID]
 ),
 CONSTRAINT [FK_OrdersV2_OrderSources] FOREIGN KEY
 (
  [OrderSourceID]
 ) REFERENCES [OrderSources] (
  [OrderSourceID]
 ),
 CONSTRAINT [FK_OrdersV2_OrderStatus] FOREIGN KEY
 (
  [OrderStatusID]
 ) REFERENCES [OrderStatus] (
  [OrderStatusID]
 ),
 CONSTRAINT [FK_OrdersV2_RegistrantsV2] FOREIGN KEY
 (
  [RegistrantID]
 ) REFERENCES [RegistrantsV2] (
  [RegistrantID]
 ),
 CONSTRAINT [FK_OrdersV2_ShippingMethods] FOREIGN KEY
 (
  [ShippingMethodID]
 ) REFERENCES [ShippingMethods] (
  [ShippingMethodID]
 )
) ON [PRIMARY]
GO

CREATE TABLE [OrderDetails] (
 [OrderDetailID] [int] IDENTITY (1, 1) NOT NULL ,
 [OrderID] [int] NOT NULL ,
 [StockID] [int] NOT NULL ,
 [QtyInOrder] [int] NOT NULL ,
 [UpdatedQtyInOrder] [int] NULL ,
 [PriceBoughtAtEach] [float] NOT NULL ,
 CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED
 (
  [OrderDetailID]
 ) ON [PRIMARY] ,
 CONSTRAINT [FK_OrderDetails_HattonsStock] FOREIGN KEY
 (
  [StockID]
 ) REFERENCES [HattonsStock] (
  [StockID]
 ),
 CONSTRAINT [FK_OrderDetails_OrdersV2] FOREIGN KEY
 (
  [OrderID]
 ) REFERENCES [OrdersV2] (
  [OrderID]
 )
) ON [PRIMARY]
GO

CREATE TABLE [PickingLists] (
 [PickingListID] [int] IDENTITY (1, 1) NOT NULL ,
 [CreatedStaffID] [int] NULL ,
 [StatusID] [int] NOT NULL ,
 [RegistrantID] [int] NOT NULL ,
 [DeliveryAddressID] [int] NOT NULL ,
 [PickingListStatusID] [int] NOT NULL ,
 [ShippingChargeID] [int] NOT NULL ,
 [PickingListNotes] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
 CONSTRAINT [PK_PickingLists] PRIMARY KEY CLUSTERED
 (
  [PickingListID]
 ) ON [PRIMARY] ,
 CONSTRAINT [FK_PickingLists_PickingListStatus] FOREIGN KEY
 (
  [PickingListStatusID]
 ) REFERENCES [PickingListStatus] (
  [PickingListStatusID]
 ),
 CONSTRAINT [FK_PickingLists_ShippingCharges] FOREIGN KEY
 (
  [ShippingChargeID]
 ) REFERENCES [ShippingCharges] (
  [ShippingChargeID]
 ),
 CONSTRAINT [FK_PickingLists_Staff] FOREIGN KEY
 (
  [CreatedStaffID]
 ) REFERENCES [Staff] (
  [StaffID]
 )
) ON [PRIMARY]
GO

CREATE TABLE [PickingListStockDetails] (
 [PickingListDetailID] [int] IDENTITY (1, 1) NOT NULL ,
 [PickingListID] [int] NOT NULL ,
 [OrderDetailID] [int] NOT NULL ,
 [QtyOnPickingList] [int] NOT NULL ,
 CONSTRAINT [PK_PickingListStockDetails] PRIMARY KEY CLUSTERED
 (
  [PickingListDetailID]
 ) ON [PRIMARY] ,
 CONSTRAINT [FK_PickingListStockDetails_OrderDetails] FOREIGN KEY
 (
  [OrderDetailID]
 ) REFERENCES [OrderDetails] (
  [OrderDetailID]
 ),
 CONSTRAINT [FK_PickingListStockDetails_PickingLists] FOREIGN KEY
 (
  [PickingListID]
 ) REFERENCES [PickingLists] (
  [PickingListID]
 )
) ON [PRIMARY]
GO

Relevant Pages

  • Re: New to SQL server
    ... [CONSTRAINT constraint_name] ... | [FOREIGN KEY] ... Is the name of the database in which the table is created. ... REFERENCES permission on the type is ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Foreign Key Problem errno 150
    ... CONSTRAINT `FK_tblstammblatt_aufsteck` FOREIGN KEY REFERENCES ... So interpretiere ich die Anleitung. ...
    (de.comp.datenbanken.mysql)
  • XSD and Bulk Load
    ... CONSTRAINT PRIMARY KEY CLUSTERED ... CONSTRAINT FOREIGN KEY ... REFERENCES ( ...
    (microsoft.public.sqlserver.xml)
  • Query optimizer question
    ... type int. ... CONSTRAINT PRIMARY KEY CLUSTERED ... CONSTRAINT FOREIGN KEY ... REFERENCES.( ...
    (microsoft.public.sqlserver.server)
  • Query optimizer question
    ... type int. ... CONSTRAINT PRIMARY KEY CLUSTERED ... CONSTRAINT FOREIGN KEY ... REFERENCES.( ...
    (microsoft.public.sqlserver.programming)