[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Restructuring an SQL query

Posted on 2008-06-10
11
Medium Priority
?
235 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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…
Suggested Courses

830 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