Solved

Restructuring an SQL query

Posted on 2008-06-10
11
197 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
  • 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Question about DB Schema 27 54
SQL Server - Set Value of Multiple Fields in One Query 10 23
SQL Server 2012 r2 - Varible Table 3 24
Query to capture 5 and 9 digit zip code? 4 20
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

770 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