title: How to get set-based solution for a difficult join
body: <p>I've got a Table with a list of Products.<br>
  I've got a Table with a list of Merchants.<br>
  I've got a Table with a list of Warehouses.  </p>
  
  <p>I've got a WarehouseMerchant Table so a a warehouse can have many merchants assigned.  </p>
  
  <p>I've got a WarehouseMerchantProduct Table so a merchant can have its products listed in a warehouse</p>
  
  <p>I've got a Merchant Link Table which can link 2 merchants</p>
  
  <pre><code>MerchantLinkID      int 
  MasterMerchantID    int 
  LinkedMerchantID    int 
  PreferenceOrder     int 
  </code></pre>
  
  <p>What i need to do is pass in a product ID and get a unique list of merchants products who can sell it.</p>
  
  <p>4 Rules</p>
  
  <ol>
  <li>If the merchant has a linked merchant who also sells the product, then don't show the linked merchants product</li>
  <li>If the merchant has a linked merchant who also sells the product, but the master merchants stock level is 0, then only show the linked merchants product</li>
  <li>In the event that a merchant has stock in multiple warehouses, then there is a preference order in the warehouseMerchant Table to select which one to use</li>
  <li>There should only be one product per merchant after this has all ran</li>
  </ol>
  
  <p>I've been racking my brains trying to figure out a solution.  I was going down the route of cursors, but it was getting a bit unwieldy.  Then i was given the 4 rules as a requirement which sent me back to the drawing board. </p>
  
  <p>If anyone can think of a quick and easy(ish) set-based solution for this then I'd love to hear it.</p>
  
  <p>Here is the script to create the tables and relationships</p>
  
  <pre><code> CREATE TABLE [dbo].[MW_Merchant](
      [MerchantID] [int] IDENTITY(1,1) NOT NULL,
      [MerchantName] [nvarchar](150) NOT NULL,
      [MerchantCode] [char](10) NOT NULL,
      [Active] [bit] NOT NULL,
   CONSTRAINT [PK_Merchant] PRIMARY KEY CLUSTERED 
  (
      [MerchantID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
   CONSTRAINT [ucMerchantCode] UNIQUE NONCLUSTERED 
  (
      [MerchantCode] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  GO
  SET ANSI_PADDING OFF
  GO
  /****** Object:  Table [dbo].[MW_Warehouse]    Script Date: 04/30/2013 09:17:39 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  SET ANSI_PADDING ON
  GO
  CREATE TABLE [dbo].[MW_Warehouse](
      [WarehouseID] [int] IDENTITY(1,1) NOT NULL,
      [WarehouseName] [nvarchar](150) NOT NULL,
      [WarehouseCode] [varchar](10) NOT NULL,
      [Active] [bit] NOT NULL,
   CONSTRAINT [PK_Warehouse] PRIMARY KEY CLUSTERED 
  (
      [WarehouseID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  GO
  SET ANSI_PADDING OFF
  GO
  /****** Object:  Table [dbo].[MW_WarehouseMerchantProduct]    Script Date: 04/30/2013 09:17:39 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  SET ANSI_PADDING ON
  GO
  CREATE TABLE [dbo].[MW_WarehouseMerchantProduct](
      [WarehouseMerchantProductID] [int] IDENTITY(1,1) NOT NULL,
      [WarehouseID] [int] NOT NULL,
      [MerchantID] [int] NOT NULL,
      [ProductCode] [varchar](30) NOT NULL,
      [StockLevel] [int] NOT NULL,
      [Active] [bit] NOT NULL,
      [PriorityOrder] [int] NOT NULL,
   CONSTRAINT [PK_MW_WarehouseProduct] PRIMARY KEY CLUSTERED 
  (
      [WarehouseMerchantProductID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  GO
  SET ANSI_PADDING OFF
  GO
  /****** Object:  Table [dbo].[MW_WarehouseMerchant]    Script Date: 04/30/2013 09:17:39 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE TABLE [dbo].[MW_WarehouseMerchant](
      [WarehouseMerchantID] [int] IDENTITY(1,1) NOT NULL,
      [WarehouseID] [int] NOT NULL,
      [MerchantID] [int] NOT NULL,
      [Active] [bit] NOT NULL,
      [PreferenceOrder] [int] NOT NULL,
   CONSTRAINT [PK_MW_WarehouseMerchant] PRIMARY KEY CLUSTERED 
  (
      [WarehouseMerchantID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  GO
  /****** Object:  Table [dbo].[MW_MerchantLink]    Script Date: 04/30/2013 09:17:39 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE TABLE [dbo].[MW_MerchantLink](
      [MerchantLinkID] [int] IDENTITY(1,1) NOT NULL,
      [MasterMerchantID] [int] NOT NULL,
      [LinkedMerchantID] [int] NOT NULL,
      [PreferenceOrder] [int] NOT NULL,
   CONSTRAINT [PK_MW_MerchantLink] PRIMARY KEY CLUSTERED 
  (
      [MerchantLinkID] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  GO
  /****** Object:  Default [DF_MW_WarehouseMerchant_PreferenceOrder]    Script Date: 04/30/2013 09:17:39 ******/
  ALTER TABLE [dbo].[MW_WarehouseMerchant] ADD  CONSTRAINT [DF_MW_WarehouseMerchant_PreferenceOrder]  DEFAULT ((10)) FOR [PreferenceOrder]
  GO
  /****** Object:  Default [DF_MW_WarehouseMerchantProduct_PriorityOrder]    Script Date: 04/30/2013 09:17:39 ******/
  ALTER TABLE [dbo].[MW_WarehouseMerchantProduct] ADD  CONSTRAINT [DF_MW_WarehouseMerchantProduct_PriorityOrder]  DEFAULT ((10)) FOR [PriorityOrder]
  GO
  /****** Object:  ForeignKey [FK_MW_MerchantLink_MW_Merchant]    Script Date: 04/30/2013 09:17:39 ******/
  ALTER TABLE [dbo].[MW_MerchantLink]  WITH CHECK ADD  CONSTRAINT [FK_MW_MerchantLink_MW_Merchant] FOREIGN KEY([MasterMerchantID])
  REFERENCES [dbo].[MW_Merchant] ([MerchantID])
  GO
  ALTER TABLE [dbo].[MW_MerchantLink] CHECK CONSTRAINT [FK_MW_MerchantLink_MW_Merchant]
  GO
  /****** Object:  ForeignKey [FK_MW_MerchantLink_MW_Merchant1]    Script Date: 04/30/2013 09:17:39 ******/
  ALTER TABLE [dbo].[MW_MerchantLink]  WITH CHECK ADD  CONSTRAINT [FK_MW_MerchantLink_MW_Merchant1] FOREIGN KEY([LinkedMerchantID])
  REFERENCES [dbo].[MW_Merchant] ([MerchantID])
  GO
  ALTER TABLE [dbo].[MW_MerchantLink] CHECK CONSTRAINT [FK_MW_MerchantLink_MW_Merchant1]
  GO
  /****** Object:  ForeignKey [FK_MW_WarehouseMerchant_MW_Merchant]    Script Date: 04/30/2013 09:17:39 ******/
  ALTER TABLE [dbo].[MW_WarehouseMerchant]  WITH CHECK ADD  CONSTRAINT [FK_MW_WarehouseMerchant_MW_Merchant] FOREIGN KEY([MerchantID])
  REFERENCES [dbo].[MW_Merchant] ([MerchantID])
  GO
  ALTER TABLE [dbo].[MW_WarehouseMerchant] CHECK CONSTRAINT [FK_MW_WarehouseMerchant_MW_Merchant]
  GO
  /****** Object:  ForeignKey [FK_MW_WarehouseMerchant_MW_Warehouse]    Script Date: 04/30/2013 09:17:39 ******/
  ALTER TABLE [dbo].[MW_WarehouseMerchant]  WITH CHECK ADD  CONSTRAINT [FK_MW_WarehouseMerchant_MW_Warehouse] FOREIGN KEY([WarehouseID])
  REFERENCES [dbo].[MW_Warehouse] ([WarehouseID])
  GO
  ALTER TABLE [dbo].[MW_WarehouseMerchant] CHECK CONSTRAINT [FK_MW_WarehouseMerchant_MW_Warehouse]
  GO
  /****** Object:  ForeignKey [FK_MW_WarehouseMerchantProduct_MW_Merchant]    Script Date: 04/30/2013 09:17:39 ******/
  ALTER TABLE [dbo].[MW_WarehouseMerchantProduct]  WITH CHECK ADD  CONSTRAINT [FK_MW_WarehouseMerchantProduct_MW_Merchant] FOREIGN KEY([MerchantID])
  REFERENCES [dbo].[MW_Merchant] ([MerchantID])
  GO
  ALTER TABLE [dbo].[MW_WarehouseMerchantProduct] CHECK CONSTRAINT [FK_MW_WarehouseMerchantProduct_MW_Merchant]
  GO
  /****** Object:  ForeignKey [FK_MW_WarehouseProduct_MW_Warehouse]    Script Date: 04/30/2013 09:17:39 ******/
  ALTER TABLE [dbo].[MW_WarehouseMerchantProduct]  WITH CHECK ADD  CONSTRAINT [FK_MW_WarehouseProduct_MW_Warehouse] FOREIGN KEY([WarehouseID])
  REFERENCES [dbo].[MW_Warehouse] ([WarehouseID])
  GO
  ALTER TABLE [dbo].[MW_WarehouseMerchantProduct] CHECK CONSTRAINT [FK_MW_WarehouseProduct_MW_Warehouse]
  GO
  </code></pre>
  
  <p>And some test data</p>
  
  <pre><code>/****** Object:  Table [dbo].[MW_Merchant]    Script Date: 04/30/2013 09:41:50 ******/
  SET IDENTITY_INSERT [dbo].[MW_Merchant] ON
  INSERT [dbo].[MW_Merchant] ([MerchantID], [MerchantName], [MerchantCode], [Active]) VALUES (24, N'Merchant 1', N'MERCH1    ', 1)
  INSERT [dbo].[MW_Merchant] ([MerchantID], [MerchantName], [MerchantCode], [Active]) VALUES (27, N'Merchant 2', N'MERCH2    ', 1)
  INSERT [dbo].[MW_Merchant] ([MerchantID], [MerchantName], [MerchantCode], [Active]) VALUES (28, N'Merchant 3', N'MERCH3    ', 1)
  SET IDENTITY_INSERT [dbo].[MW_Merchant] OFF
  /****** Object:  Table [dbo].[MW_Warehouse]    Script Date: 04/30/2013 09:41:50 ******/
  SET IDENTITY_INSERT [dbo].[MW_Warehouse] ON
  INSERT [dbo].[MW_Warehouse] ([WarehouseID], [WarehouseName], [WarehouseCode], [Active]) VALUES (12, N'Warehouse 1', N'WARE1', 1)
  INSERT [dbo].[MW_Warehouse] ([WarehouseID], [WarehouseName], [WarehouseCode], [Active]) VALUES (13, N'Warehouse 2', N'WARE2', 1)
  INSERT [dbo].[MW_Warehouse] ([WarehouseID], [WarehouseName], [WarehouseCode], [Active]) VALUES (14, N'Warehouse 3', N'WARE3', 1)
  SET IDENTITY_INSERT [dbo].[MW_Warehouse] OFF
  /****** Object:  Table [dbo].[MW_WarehouseMerchantProduct]    Script Date: 04/30/2013 09:41:50 ******/
  SET IDENTITY_INSERT [dbo].[MW_WarehouseMerchantProduct] ON
  INSERT [dbo].[MW_WarehouseMerchantProduct] ([WarehouseMerchantProductID], [WarehouseID], [MerchantID], [ProductCode], [StockLevel], [Active], [PriorityOrder]) VALUES (93, 13, 24, N'TESTPRODUCT', 20, 1, 10)
  INSERT [dbo].[MW_WarehouseMerchantProduct] ([WarehouseMerchantProductID], [WarehouseID], [MerchantID], [ProductCode], [StockLevel], [Active], [PriorityOrder]) VALUES (96, 14, 24, N'TESTPRODUCT', 20, 1, 10)
  INSERT [dbo].[MW_WarehouseMerchantProduct] ([WarehouseMerchantProductID], [WarehouseID], [MerchantID], [ProductCode], [StockLevel], [Active], [PriorityOrder]) VALUES (97, 14, 27, N'TESTPRODUCT', 10, 1, 10)
  SET IDENTITY_INSERT [dbo].[MW_WarehouseMerchantProduct] OFF
  /****** Object:  Table [dbo].[MW_MerchantLink]    Script Date: 04/30/2013 09:41:50 ******/
  SET IDENTITY_INSERT [dbo].[MW_MerchantLink] ON
  INSERT [dbo].[MW_MerchantLink] ([MerchantLinkID], [MasterMerchantID], [LinkedMerchantID], [PreferenceOrder]) VALUES (22, 24, 27, 1)
  INSERT [dbo].[MW_MerchantLink] ([MerchantLinkID], [MasterMerchantID], [LinkedMerchantID], [PreferenceOrder]) VALUES (23, 24, 28, 2)
  SET IDENTITY_INSERT [dbo].[MW_MerchantLink] OFF
  </code></pre>
  
  <p>In the example data, my returned row should be the WarehouseMerchantProductID 93
  93,96 and 97 all have stock, however</p>
  
  <p>93 and 96 belong to the same merchant and should be selected based on PreferenceOrder in the WarehouseMerchant Table</p>
  
  <p>Merchant 27 is linked to 24 so shouldnt appear (unless 24 goes out of stock)</p>