Re: Three Table Join

[Jo Pitts]

Peter,
the situation I described is deliberately an example, as the real data is fairly confidential.

However, here are the table structures for my notional example data sets.

Table: Week_Ref
Columns:
WeekID Int(11)
WeekName Varchar(30)

Table: Customer
Columns:
CustomerID Int(11)
CustomerName Varchar(50)

Table: Sales
Columns:
SaleID Int(11)
CustomerID Int(11)
WeekID Int(11)
SaleAmount Decimal(10,2)

Data for Week:
WeekID | WeekName
1 | One
2 | Two

Data for Customer:
CustomerID | CustomerName
1 | Bob
2 | Mike
3 | Jane

Data for Sales:
SaleID | WeekID | CustomerID | Amount
1 | 1 | 1 | 50.00
2 | 1 | 1 | 13.00
3 | 1 | 3 | 25.00
4 | 2 | 3 | 18.00
5 | 2 | 3 | 75.00

Query Thus Far
select
*
from
sales
right outer join Customer on Customer.CustomerID = Sales.CustomerID
order by
Customer.CustomerName

Which gives me a list of all customers regardless of whether they've purchased. I can add the week in with another right outer join, but I don't get the desired result.

Regards
Jo