Help extracting from multiple tables

Transact-SQL
1

Hi There
Not being a guru I thought I'd reach out to see if anyone can solve the issue I have.
I have 2 tables. One contains accounts that have a master price code for deliveries to towns and a master town code. Similar to this.
ANum | BName | PriceCode | Town
---------------------------------
1 | MS | AB | AL
---------------------------------
2 | GOO | N5 | BY
---------------------------------
3 | ORC | H7 | KG
---------------------------------
4 | SYM | AB | BY
---------------------------------
5 | VER | U4 | MD
---------------------------------

For most accounts they have just one price code.
I have another table that links with the ANum that holds a list of additional price codes for different towns the account may service. Like this
ANum | PriceCode | Town
---------------------------------
1 | R5 | KG
---------------------------------
1 | E3 | BU
---------------------------------
1 | E4 | FR
---------------------------------
2 | H7 | KO
---------------------------------
4 | U4 | DE
---------------------------------
4 | F2 | AL
---------------------------------
5 | AB | AL
---------------------------------
5 | AB | KO
---------------------------------

I'd like to get a list of all accounts that use a specific price code...say all that use AB. I can use a join but then I end up with 2 rows for the ANum 5 as it has 2 entries in the additional list. I'd like to get the accounts and then maybe count how many additional entries are found so the result would be
ANum | BName | PriceCode | Town | Additional
------------------------------------------
1 | MS | AB | AL | 0
------------------------------------------
5 | VER | U4 | MD | 2
------------------------------------------

is this possible?
Cheers
Peter

2

Can you try this:

select a.anum,a.bname,a.pricecode,count(p.anum) as Additional
from accounts a
inner join price p on a.anum=p.anum
where a.pricecode='AB'
group by a.anum,a.bname,a.pricecode

3

Hi ahmeds

It is close but not correct. The results I get are
anum bname pricecode Additional
1 MS AB 3
4 SYM AB 2

My original take on the answer wasn't quite correct either now that I've looked at it. It should return a result like

 ANum | BName | PriceCode | Town | Additional
 ------------------------------------------
 1   | MS    | AB        | AL    | 0
 ------------------------------------------
 4   | SYM   | AB        | BY    | 0
 ------------------------------------------
 5   | VER   | U4        | MD    | 2
 ------------------------------------------

Both account 1 & 4 have AB as a primary and account 5 has 2 as additional.

Cheers
Peter

4

After having a think last night I worked out I can do a UNION ALL on 2 selects, first for primary on accounts table and second for additional on the prices table. Seems to be working well now. Here is the actual SP I finished up with.

USE [Freight]
GO
/****** Object:  StoredProcedure [dbo].[xfsp_GetListOfAccounts12]    Script Date: 31/10/2016 8:45:56 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[xfsp_GetListOfAccounts12]
@TownCode varchar(2),
@PriceCode varchar(2),
@AccountType tinyint,
@BadPayers tinyint,
@Suspended tinyint

AS
BEGIN
	SET NOCOUNT ON;

	SELECT 
	a.SupplierOnly,
	a.BadPayer,
	a.IsSuspended,
	a.AccountCode,
	a.AccountNumber,
	a.BusinessName,
	a.Town,
	a.Postcode,
	a.Phone_BH,
	a.Balance,
	a.CurrentInvoice,
	0 as Additional,
	a.PriceCode as PPrCode,
	a.DepotCode as PTCode
	FROM Accounts a
	WHERE a.DepotCode LIKE @TownCode
	AND a.PriceCode LIKE @PriceCode
	AND (@BadPayers=2 OR a.BadPayer=@BadPayers)
	AND (@Suspended=2 OR a.IsSuspended=@Suspended)
	AND (@AccountType=2 OR a.SupplierOnly=@AccountType)

	UNION ALL

	SELECT 
	a.SupplierOnly,
	a.BadPayer,
	a.IsSuspended,
	a.AccountCode,
	a.AccountNumber,
	a.BusinessName,
	a.Town,
	a.Postcode,
	a.Phone_BH,
	a.Balance,
	a.CurrentInvoice,
	count(d.accountnumber) as Additional,
	@PriceCode as PPrCode,
	'' as PTCode
	from DepotPrice d
	join accounts a on a.AccountNumber=d.AccountNumber
	where d.PriceCode = @PriceCode
	AND (@BadPayers=2 OR a.BadPayer=@BadPayers)
	AND (@Suspended=2 OR a.IsSuspended=@Suspended)
	AND (@AccountType=2 OR a.SupplierOnly=@AccountType)
	group by
	a.BusinessName,
	a.AccountNumber,
	a.SupplierOnly,
	a.BadPayer,
	a.IsSuspended,
	a.AccountCode,
	a.Town,
	a.Postcode,
	a.Phone_BH,
	a.Balance,
	a.CurrentInvoice
	ORDER BY a.BusinessName

END
5

I wonder if that might be better as a straight COUNT(*) - i.e. a count of all rows. I think there is a risk that naming a specific column forces SQL to use a query plan where it might otherwise choose something else (which is more efficient).

But I'm just guessing ... but if that is indeed a "thing" then the original might not scale well.

Personally I would have written the JOIN with [accounts] as the primary table. It makes no difference at all to the query (unless you decided to change the JOIN so that [DepotPrice] becomes an outer join), I just think its more readable - given that the first UNION is FROM [accounts]

from accounts a 
	joinDepotPrice d on d.AccountNumber = a.AccountNumber

on that basis I suppose you might be able to do it in one strike by using an OUTER JOIN - although SQL may make a dreadful query plan for this approach

SELECT 
	a.SupplierOnly,
	a.BadPayer,
	a.IsSuspended,
	a.AccountCode,
	a.AccountNumber,
	a.BusinessName,
	a.Town,
	a.Postcode,
	a.Phone_BH,
	a.Balance,
	a.CurrentInvoice,
	count(d.accountnumber) as Additional,
-- Need to do soemthing for these two columns!!
	@PriceCode as PPrCode,
	'' as PTCode
	from accounts a
	LEFT OUTER join DepotPrice d on d.AccountNumber = a.AccountNumber
	where (
		   (d.AccountNumber IS NOT NULL AND d.PriceCode = @PriceCode)
		OR (d.AccountNumber IS NULL AND a.PriceCode LIKE @PriceCode)
	)
	AND (@BadPayers=2 OR a.BadPayer=@BadPayers)
	AND (@Suspended=2 OR a.IsSuspended=@Suspended)
	AND (@AccountType=2 OR a.SupplierOnly=@AccountType)
	group by
	a.BusinessName,
	a.AccountNumber,
	a.SupplierOnly,
	a.BadPayer,
	a.IsSuspended,
	a.AccountCode,
	a.Town,
	a.Postcode,
	a.Phone_BH,
	a.Balance,
	a.CurrentInvoice
	ORDER BY a.BusinessName

The behaviour of this isn't quite right because the UNION ALL will include an account TWICE if it has both

a.PriceCode LIKE @PriceCode

and also has associated [DepotPrice] rows with d.PriceCode = @PriceCode

But there might be some merit combining them (provided SQL doesn't use a lousy Query Plan as a consequence)

6

Hi Kristen
Thank you for your response. I'll tweak a couple of things based on your comments. I've run both queries and it's so fast both ways it's hard to differentiate. Not sure how to sort out the 2 columns as users can enter a pricecode or not (blank gets replaced with %), same with the towncode. You are also correct in that duplicate business names will occur and while it "shouldn't" as it does little if the master pricecode is the same it has highlighted a couple of instances, so I can go back to the users and get them to fix it and update my code so it does some extra checks on these additional codes.

I was going to copy the Execution plans for both queries but can't paste direct, and I'll have to copy them somewhere and put in a link.

7

Might be its not worth the bother (unless you are on a small scale test, and Production will be much bigger scale?), but you could just check the Logical Reads and number of SCANS between the two, and if they are dramatically different consider investigating further.

 SET STATISTICS IO ON; SET STATISTICS TIME ON

-- ... put query here ...

SET STATISTICS IO OFF; SET STATISTICS TIME OFF; SET STATISTICS PROFILE OFF
GO

Ignore anything except the Logical Reads and SCANs

We do that by passing NULL as the parameter (meaning "anything"), but BLANK or whatever would do (just needs to be something that the user could never actually want to match!!) and in there WHERE clause we would do:

WHERE     (@MyParam1 IS NULL OR MyCol1 = @MyParam1)
      AND (@MyParam2 IS NULL OR MyCol1 LIKE @MyParam2 + '%')
      ...
1 Like
8

Hi Kristen
Thank you again. There is not a lot in the statistics and given there are really only about 48,000 rows it's not a huge amount of data to process. The stats for the UNION query

   Table 'Accounts'. Scan count 1, logical reads 279
   Table 'DepotPrice'. Scan count 1, logical reads 11

and for the JOIN query

   Table 'Accounts'. Scan count 1, logical reads 520
   Table 'DepotPrice'. Scan count 1, logical reads 11

The parameters return about 30 rows from the 48,000 and if I use parameters that return all 48,000 rows the JOIN query takes about 3 seconds to process whereas the UNION takes around a second.

Anyway it has given me a bit more insight into different ways of doing the same function so will play around a bit more and see if I can improve on my current routine.

Cheers
Peter

1 Like