Solved

Restructuring an SQL query

Posted on 2008-06-10
11
191 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

762 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now