Customer can only have 01 orderOnSale subquery / CROSS APPLY in SQL Server 2008

Question

I have created a dummy scenario that reflect many of the queries I have to write to check that some data we are importing is correct.

The example would be when you have 3 tables

1. Store
2. Customer
3. CustomerOrder

A Customer can belong to many stores but can only 1 OrderOnsale can be bought x customer x store.

Cannot seem to get it right. Below is tables and noddy data + my attempt.

IF object_id(N'Store', 'U') IS NOT NULL
   DROP TABLE Store
GO

CREATE TABLE [dbo].[Store]
(
    [Id] [bigint] NOT NULL,
    [StoreName] [varchar](50) NOT NULL,
    CONSTRAINT [PK_Store] PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]
GO

IF object_id(N'Customer', 'U') IS NOT NULL
   DROP TABLE Customer
GO

CREATE TABLE [dbo].[Customer]
(
    [CustomerId] [bigint] NOT NULL,
    [StoreId] [bigint] NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [Surname] [varchar](50) NOT NULL,
    CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ([CustomerId] ASC)
) ON [PRIMARY]
GO

IF object_id(N'CustomerOrder', 'U') IS NOT NULL
   DROP TABLE CustomerOrder
GO

CREATE TABLE [dbo].[CustomerOrder]
(
    [OrderId] [bigint] NOT NULL,
    [CustomerId] [bigint] NOT NULL,
    [OrderName] [varchar](50) NOT NULL,
    [OnSale] [bit] NOT NULL,
    CONSTRAINT [PK_CustomerOrder] PRIMARY KEY CLUSTERED([OrderId] ASC)
) ON [PRIMARY]
GO

begin tran
   INSERT INTO [dbo].[Store]([Id], [StoreName])
        SELECT 1, N'Harrods' UNION ALL
        SELECT 2, N'John Lewis'

        INSERT INTO [dbo].[Customer]([CustomerId], [StoreId], [Name], [Surname])
        SELECT 1, 1, N'John', N'Smith' UNION ALL
        SELECT 2, 2, N'Joe', N'Blogg'

        INSERT INTO [dbo].[CustomerOrder]([OrderId], [CustomerId], [OrderName], [OnSale])
        SELECT 1, 1, N'Toys', 1 UNION ALL
        SELECT 2, 1, N'Laptop', 1 UNION ALL
        SELECT 3, 2, N'Toys', 0

        commit

My incomplete attempt:

SELECT
   HasCustomerBoughtMoreThanO1ItemOnSale =
      CASE WHEN Count(T2.TotalBoughtOnSale) > 1 THEN 1 ELSE 0 END 
FROM 
   CustomerOrder co1 
INNER JOIN 
   customer c1 ON co1.CustomerId = c1.CustomerId
INNER JOIN 
   STORE S01 ON C1.StoreId = S01.Id
JOIN 
   (SELECT 
       CO2.CustomerId, S2.Id AS StoreId, 
       Count(CO2.OnSale) TotalBoughtOnSale
    FROM 
       CustomerOrder CO2
    INNER JOIN 
       customer c2 ON c2.CustomerId = CO2.CustomerId
    INNER JOIN 
       STORE S2 ON C2.StoreId = S2.Id
    WHERE 
       CO2.OnSale = 1       
    GROUP BY 
       CO2.CustomerId, S2.Id) AS t2 ON c1.CustomerId = T2.CustomerId 
                                    AND S01.Id = t2.StoreId

Answer 1

If what your after is if a single customer has bought more then one OnSale item then this query will do the trick.

SELECT
CO.CustomerId, C.StoreId
FROM CustomerOrder CO
INNER JOIN Customer C ON CO.CustomerId = C.CustomerId
WHERE OnSale = 1
GROUP BY CO.CustomerId, C.StoreId
HAVING COUNT(*) > 1

I should add that in this its given that a Customer can only shop in a single Store due to StoreId is a column in the Customer table.