Solved

Restructuring an SQL query

Posted on 2008-06-10
11
194 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

911 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

24 Experts available now in Live!

Get 1:1 Help Now