[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Restructuring an SQL query

Posted on 2008-06-10
11
Medium Priority
?
228 Views
Last Modified: 2008-07-16
Dear Experts,

I wrote the query below a while back to select all products which are associated with an outlet.

I now wish to add in another layer which only selects a product when product group is in table 'ProductGroupTerritories' (which has fields; Group_ID and Territory_ID)

If the group_id and territory of the outlet aren't in this table, I don't want it returned to the user.

Could anybody provide some logic assistance with this query? I'm really struggling today!!

Many thanks in advance,

Nick
SELECT DISTINCT 
 c.ProductID AS Product_ID, 
 c.ProductName AS Product_Name, 
 ProductSuppliers.SupplierName AS Product_Supplier, 
 AccountOutlets.Outlet_ID, 
 ProductSuppliers.SupplierId, 
 pg.GroupOrderable, 
 @Username as Username, 
 c.Group_ID,
 OutletList.Territory_ID
FROM AccountOutlets  
INNER JOIN ClientAccounts AS a ON AccountOutlets.Account_ID = a.Account_ID 
INNER JOIN OutletList ON AccountOutlets.Outlet_ID = OutletList.Outlet_ID
RIGHT OUTER JOIN Products AS c 
INNER JOIN ProductGroups pg ON pg.GroupID=c.Group_ID 
INNER JOIN ProductSuppliers ON c.SupplierID = ProductSuppliers.SupplierID 
LEFT OUTER JOIN AccountOutletProducts AS aocf ON c.ProductID = aocf.Product_ID AND aocf.Account_ID IN (SELECT Account_ID 
FROM @OutletList) ON a.Account_ID = aocf.Account_ID
WHERE (AccountOutlets.Outlet_ID IN (Select Outlet_ID FROM @OutletList)

Open in new window

0
Comment
Question by:nkewney
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 16

Expert Comment

by:brad2575
ID: 21752663
Just add an inner join to that table on the group_id.  The inner join will do the work for you.

If the join does not find the data in the ProductGroupTerritories it will not return that row of data.
0
 
LVL 6

Expert Comment

by:J_Carter
ID: 21752712
Here's the inner join and a check to make sure the territory id is not null
SELECT DISTINCT 
 c.ProductID AS Product_ID, 
 c.ProductName AS Product_Name, 
 ProductSuppliers.SupplierName AS Product_Supplier, 
 AccountOutlets.Outlet_ID, 
 ProductSuppliers.SupplierId, 
 pg.GroupOrderable, 
 @Username as Username, 
 c.Group_ID,
 OutletList.Territory_ID
FROM AccountOutlets  
INNER JOIN ClientAccounts AS a ON AccountOutlets.Account_ID = a.Account_ID 
INNER JOIN OutletList ON AccountOutlets.Outlet_ID = OutletList.Outlet_ID
RIGHT OUTER JOIN Products AS c 
INNER JOIN ProductGroups pg ON pg.GroupID=c.Group_ID 
INNER JOIN ProductSuppliers ON c.SupplierID = ProductSuppliers.SupplierID 
inner join ProductGroupTerritories pgt on pgt.Group_ID = pg.GroupID and pgt.Territory_ID is not null
LEFT OUTER JOIN AccountOutletProducts AS aocf ON c.ProductID = aocf.Product_ID AND aocf.Account_ID IN (SELECT Account_ID 
FROM @OutletList) ON a.Account_ID = aocf.Account_ID
WHERE (AccountOutlets.Outlet_ID IN (Select Outlet_ID FROM @OutletList)

Open in new window

0
 
LVL 1

Author Comment

by:nkewney
ID: 21753353
Hi,

Thanks for the quick responses - that makes sense.

This only seems to filter it by Group_ID... if a record doesn't exist in 'ProductGroupTerritories' with both Territory_ID and Group ID of the current row, I don't want to return the record.

Nick
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 6

Expert Comment

by:J_Carter
ID: 21753446
ok, where I have "and pgt.Territory_ID is not null" change that to "and pgt.Territory_ID = OutletList.Territory_ID".

That should do it.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21753473
You might need to elaborate on the new table a little bit... but it sound like an inner join on product group and territory id - but you go on to say group_id and Territory from the outlet - could you please clarify ?

Also, that right outer join is an interesting beast - are you sure that is what you need ? The reason being the inner join using outlet criteria would seem logical to place with outlet list , though, if it is really getting attributes from product, then it ould appear after the right join...
0
 
LVL 1

Author Comment

by:nkewney
ID: 21754083
Thanks guys,

This line doesn't seem to work

inner join ProductGroupTerritories pgt on pgt.Group_ID = pg.GroupID and pgt.Territory_ID = OutletList.Territory_ID

The multi-part identifier "ol.Territory_ID" could not be bound.

Any ideas why this might be?

Thanks

NIck
0
 
LVL 1

Author Comment

by:nkewney
ID: 21754167
Sorry, the error is: The multi-part identifier "OutletList.Territory_ID" could not be bound.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21754201
You do need to explain which columns are which... Please see my earlier post. Just guessing otherwise.
0
 
LVL 1

Author Comment

by:nkewney
ID: 21754284
Hi Mark,

I have listed the table CREATE statements below.

Does this help at all?

Thanks

Nick
USE [live]
GO
/****** Object:  Table [dbo].[OutletList]    Script Date: 06/10/2008 20:30:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OutletList](
	[Outlet_ID] [int] IDENTITY(1,1) NOT NULL,
	[Channel_ID] [int] NULL,
	[Outlet_Code] [varchar](50) NULL,
	[Outlet_Name] [varchar](50) NULL,
	[Outlet_Location] [varchar](50) NULL,
	[Outlet_Address_1] [varchar](50) NULL,
	[Outlet_Address_2] [varchar](50) NULL,
	[Outlet_City] [varchar](50) NULL,
	[Outlet_County] [varchar](50) NULL,
	[Outlet_Postcode] [varchar](50) NULL,
	[Outlet_Country] [varchar](50) NULL,
	[Outlet_Phone] [varchar](50) NULL,
	[Outlet_Fax] [varchar](50) NULL,
	[Created] [datetime] NULL,
	[Creator] [varchar](50) NULL,
	[ManagedBy] [varchar](50) NULL,
	[Territory_ID] [int] NULL,
	[AutoCreateNewCall] [bit] NULL,
 CONSTRAINT [PK_AccountOutlets] PRIMARY KEY CLUSTERED 
(
	[Outlet_ID] 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
 
USE [live]
GO
/****** Object:  Table [dbo].[ProductGroupTerritories]    Script Date: 06/10/2008 20:30:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProductGroupTerritories](
	[Group_ID] [int] NULL,
	[Territory_ID] [int] NULL
) ON [PRIMARY]
 
 
USE [live]
GO
/****** Object:  Table [dbo].[Products]    Script Date: 06/10/2008 20:30:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Products](
	[ProductID] [int] IDENTITY(1,1) NOT NULL,
	[Company_ID] [int] NULL,
	[CategoryID] [int] NULL,
	[SupplierID] [int] NULL,
	[ManufacturerID] [int] NULL,
	[ProductName] [varchar](100) NULL,
	[ProductSKU] [varchar](100) NULL,
	[Group_ID] [int] NULL,
	[Price] [money] NULL,
	[Created] [datetime] NULL,
	[Creator] [varchar](50) NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
	[ProductID] 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

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21756480
Yep, territory is from outlet and group is from product... Therfore:
SELECT DISTINCT 
 c.ProductID AS Product_ID, 
 c.ProductName AS Product_Name, 
 ProductSuppliers.SupplierName AS Product_Supplier, 
 AccountOutlets.Outlet_ID, 
 ProductSuppliers.SupplierId, 
 pg.GroupOrderable, 
 @Username as Username, 
 c.Group_ID,
 OutletList.Territory_ID
FROM AccountOutlets  
INNER JOIN ClientAccounts AS a ON AccountOutlets.Account_ID = a.Account_ID 
INNER JOIN OutletList ON AccountOutlets.Outlet_ID = OutletList.Outlet_ID
RIGHT OUTER JOIN Products AS c 
INNER JOIN ProductGroups pg ON pg.GroupID=c.Group_ID 
INNER JOIN ProductSuppliers ON c.SupplierID = ProductSuppliers.SupplierID 
inner join ProductGroupTerritories pgt on pgt.Group_ID = c.Group_ID and pgt.Territory_ID = OutletList.Territory_ID
LEFT OUTER JOIN AccountOutletProducts AS aocf ON c.ProductID = aocf.Product_ID AND aocf.Account_ID IN (SELECT Account_ID 
FROM @OutletList) ON a.Account_ID = aocf.Account_ID
WHERE (AccountOutlets.Outlet_ID IN (Select Outlet_ID FROM @OutletList)

Open in new window

0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 21756499
And, still a bit worried about your right join, so, looking at your requirement again, maybe this will work a bit better:


SELECT DISTINCT 
 c.ProductID AS Product_ID, 
 c.ProductName AS Product_Name, 
 ProductSuppliers.SupplierName AS Product_Supplier, 
 AccountOutlets.Outlet_ID, 
 ProductSuppliers.SupplierId, 
 pg.GroupOrderable, 
 @Username as Username, 
 c.Group_ID,
 OutletList.Territory_ID
FROM Products AS c 
INNER JOIN ProductGroups pg ON pg.GroupID=c.Group_ID 
INNER JOIN ProductSuppliers ON c.SupplierID = ProductSuppliers.SupplierID 
inner join ProductGroupTerritories pgt on pgt.Group_ID = c.Group_ID 
inner join OutletList on pgt.Territory_ID = OutletList.Territory_ID
inner join AccountOutlets on AccountOutlets.Outlet_ID = OutletList.Outlet_ID
inner join ClientAccounts AS a ON AccountOutlets.Account_ID = a.Account_ID 
 
LEFT OUTER JOIN AccountOutletProducts AS aocf ON c.ProductID = aocf.Product_ID AND aocf.Account_ID IN (SELECT Account_ID FROM @OutletList) ON a.Account_ID = aocf.Account_ID
WHERE (AccountOutlets.Outlet_ID IN (Select Outlet_ID FROM @OutletList)

Open in new window

0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question