SQLTeam.com | Weblogs | Forums

Select All records including those with no match


#1

Greetings. I would like to select data from 3 tables, Customer, Periods, and Sales.
My Sales table has a Customer and a Period and a Sales Value, and I can select data as follows....
Select C.CustomerCode, C.CustomerTitle, S.Periodno, P.PeriodName, S.PeriodSales from ((Customers C Left Outer join CSales S on C.UniqueId =S.CustomerId) left outer join Periods P on S.Periodno=P.PeriodNumber)

But if a Customer only has sales in Period 1 and 3 I only get data for Periods 1 and 3
Is there any way that I can get data for periods 1 2 and 3 (with Period 2 showing 0 value)

Note: Also tried the following without sucess
Select C.CustomerCode, C.CustomerTitle, S.Periodno, P.PeriodName, S.PeriodSales from ((Periods P left outer join CSales S on P.PeriodNumber = S.Periodno) left outer join Customers C on S.CustomerId=C.UniqueId)


#2

do you have sample data?


#3

This perhaps?

Select	C.CustomerCode, C.CustomerTitle, S.Periodno, P.PeriodName, S.PeriodSales 
from	Customers AS C 
	Left Outer join CSales AS S 
		on S.CustomerId = C.UniqueId
	left outer join Periods AS P 
		on P.PeriodNumber = S.Periodno
WHERE	S.CustomerId IN
	(
		SELECT	DISTINCT C.UniqueId
		FROM	Periods AS P
			JOIN CSales AS S
				 ON S.Periodno = P.PeriodNumber
		WHERE	C.UniqueId = S.CustomerId
--			AND P.PeriodNumber >= @StartPeriod AND P.PeriodNumber <= @EndPeriod
	)

This will select any customer who has any sales within the period (I've added a commented-out range for Period Number, without that it will any customer with any sale in any period)

Please format any code in your posts using the </> button to make it easier for all of us to read it :slightly_smiling:


#4

Thanks - tried that and it didnt give me all periods either.
To clarify further.... say the data in Customers is C1 and in Periods we have 1, 2, 3 and 4
If the data in CSales for C1 is $100 in Period 1 and $50 in Period 3 I am trying to find a selection that will give me 3 records....
C1 Period1 $100
C1 Period2 $0
C1 Period3 $50
C1 Period4 $0


#5

Whoops - sorry - I meant gives me 4 records


#6

More information and an additional idea that might work.
There is a field called Uniqueid in CSales that consists of the CustomerCode concatinated with the Periodno.
If we can select all periods for all Customers then that would work.
How to change the following so that it would run?

`

Select C.CustomerCode, C.CustomerTitle, S.Periodno,
S.PeriodSales from Customers C Left Outer join CSales S on
RTrim(C.UniqueId)+str(Periodnumber) =S.UniqueId where Periodnumber in (Select Periodnumber from Periods)

`


#7

This solution works....I forced a join between the Customer and Period tables (by using unrelated fields but made them equate)

Select C.CustomerCode, C.CustomerTitle, P.Periodnumber, S.PeriodSales from Customers C inner join Periods P on C.Groupno-C.Groupno = P.Periodnumber-P.Periodnumber Left Outer join CSales S on RTrim(C.UniqueId)+Ltrim(str(P.Periodnumber,10)) =S.UniqueId Order by C.CustomerCode, P.Periodnumber


#8

Try this instead:
EDIT: CODE MODIFIED!

Select	C.CustomerCode, C.CustomerTitle, S.Periodno, P.PeriodName, S.PeriodSales 
from	Periods AS P 
	join Customers AS C 
		ON C.UniqueId IN
		(
			SELECT	DISTINCT S.CustomerId
			FROM	Periods AS P
				JOIN CSales AS S
					 ON S.Periodno = P.PeriodNumber
--			WHERE	P.PeriodNumber >= @StartPeriod AND P.PeriodNumber <= @EndPeriod
		)
	Left Outer join CSales AS S 
		 on S.Periodno = P.PeriodNumber
		AND S.CustomerId = C.UniqueId

It would help if you posted some sample data - in the form of a CREATE TABLE DDL for the three tables and some INSERT statements to put the sample data in them, then folk here can use that to test solutions. Otherwise we will all, separately, be having to do that and you won't get answers from people that don't have time to do that ... in my case without that example data I'm just guessing and my code probably has syntax errors too ... as I definitely don't have time to create sample data as well ...


#9

Thanks everyone for your help