Joining three tables and trying to get null values

I have three tables: tblProduct, lkpFoodgroup, tblCustomer. And one junction table: jctCustomerFoodgroup

The columns are like this:

**tblProduct**
+---+----------------+
|PK |int_ProductID   |
|FK |int_FoodgroupID |
|   |str_ProductName |
+---+----------------+

**lkpFoodgroup**
+---+-------------------+
|PK |int_FoodgroupID    |
|   |str_FoodgroupHandle|
+---+-------------------+

**tblCustomer**
+---+----------------+
|PK |int_CustomerID  |
|   |str_CustomerName|
+---+----------------+

**jctCustomerFoodgroup**
+---+----------------+
|PK |int_CustomerID  |
|PK |int_FoodgroupID |
|   |int_ProductID   |
+---+----------------+

The simplest of these tables is the lookup:

**lkpFoodgroup**
+---------------+-------------------+
|int_FoodgroupID|str_FoodgroupHandle|
+---------------+-------------------+
|1              |fruit              |
|2              |meat               |
|3              |bread              |
|4              |cheese             |
+---------------+-------------------+

Next is Customer:

**tblCustomer**
+----------------+-------------------+
|int_CustomerID  |str_CustomerName   |
+----------------+-------------------+
|1               |Bob                |
|2               |Sally              |
|3               |Jane               |
|4               |Billy              |
+----------------+-------------------+

There can be many products with the same Foodgroup on tblProduct. Also there can be some product Foodgroups with no products in them:

**tblProduct**
+---------------+-----------------+----------------+
|int_ProductID  |int_FoodgroupID  |str_ProductName |
+---------------+-----------------+----------------+
|1              |1                |apple           |
|2              |1                |banana          |
|3              |1                |orange          |
|4              |1                |pear            |
|5              |2                |chicken         |
|6              |2                |beef            |
|7              |2                |fish            |
|8              |2                |turkey          |
|9              |3                |white           |
|10             |3                |wheat           |
+---------------+-----------------+----------------+

The PK on the junction table is a combined int_CustomerID and int_FoodgroupID - which means that any customer can only choose one Product per Foodgroup:

**jctCustomerFoodgroup**
+---------------+-----------------+--------------+------------------------+
|int_CustomerID |int_FoodgroupID  |int_ProductID |  --meaning             |
+---------------+-----------------+--------------+------------------------|
|1              | 1               |1             | --Bob, fruit, apple    |
|1              | 2               |6             | --Bob, meat, beef      |
|1              | 3               |9             | --Bob, bread, white    |
|2              | 1               |3             | --Sally, fruit, orange |
|2              | 2               |5             | --Sally, meat, chicken |
|3              | 1               |3             | --Jane, fruit, orange  |
|3              | 3               |9             | --Jane, bread, white   |
|3              | 2               |6             | --Jane, meat, beef     |
+---------------+-----------------+--------------+------------------------+

I am looking for a query which will give me results like this:

**spGetCustomerProductSelections(1) --Get Bob's choices**
+----------------+---------------+-------------------+-------------+---------------+
|int_CustomerID  |int_FoodgroupID|str_FoodgroupHandle|int_ProductID|str_ProductName|
+----------------+---------------+-------------------+-------------+---------------+
|1               |1              |fruit              |1            |apple          |
|1               |2              |meat               |6            |beef           |
|1               |3              |bread              |9            |white          |
|1               |4              |cheese             |null         |null           |
+----------------+---------------+-------------------+-------------+---------------+

**spGetCustomerProductSelections(2) --Get Sally's choices**
+----------------+---------------+-------------------+-------------+---------------+
|int_CustomerID  |int_FoodgroupID|str_FoodgroupHandle|int_ProductID|str_ProductName|
+----------------+---------------+-------------------+-------------+---------------+
|2               |1              |fruit              |3            |orange         |
|2               |2              |meat               |5            |chicken        |
|2               |3              |bread             开发者_如何学Go |null         |null           |
|2               |4              |cheese             |null         |null           |
+----------------+---------------+-------------------+-------------+---------------+

**spGetCustomerProductSelections(4) --Get Billy's choices**
+----------------+---------------+-------------------+-------------+---------------+
|int_CustomerID  |int_FoodgroupID|str_FoodgroupHandle|int_ProductID|str_ProductName|
+----------------+---------------+-------------------+-------------+---------------+
|4               |1              |fruit              |null         |null           |
|4               |2              |meat               |null         |null           |
|4               |3              |bread              |null         |null           |
|4               |4              |cheese             |null         |null           |
+----------------+---------------+-------------------+-------------+---------------+

Any help?

---

Please, do not name your procedures beginning with "sp". It will start searching in the master database and only later come back to your database.

DDL for your schema and data

create table lkpFoodgroup
(int_FoodgroupID int, str_FoodgroupHandle varchar(max))
insert lkpFoodgroup values
(1,'fruit'),
(2,'meat'),
(3,'bread'),
(4,'cheese');

create table tblCustomer
(int_CustomerID int, str_CustomerName varchar(max))
insert tblCustomer values
(1,'Bob'),
(2,'Sally'),
(3,'Jane'),
(4,'Billy');

create table tblProduct
(int_ProductID int, int_FoodgroupID int, str_ProductName varchar(max))
insert tblProduct values
(1,'1','apple'),
(2,'1','banana'),
(3,'1','orange'),
(4,'1','pear'),
(5,'2','chicken'),
(6,'2','beef'),
(7,'2','fish'),
(8,'2','turkey'),
(9,'3','white'),
(10,'3','wheat');

create table jctCustomerFoodgroup
(int_CustomerID int, int_FoodgroupID int, int_ProductID varchar(max))
insert jctCustomerFoodgroup values
(1,'1','1'),
(1,'2','6'),
(1,'3','9'),
(2,'1','3'),
(2,'2','5'),
(3,'1','3'),
(3,'3','9'),
(3,'2','6');

The Stored Proc code

create proc uspGetCustomerProductSelections @customerID int as
select c.int_CustomerID, l.int_FoodgroupID, l.str_FoodgroupHandle, j.int_ProductID, p.str_ProductName
from tblCustomer c
cross join lkpFoodgroup l
left join jctCustomerFoodgroup j on j.int_CustomerID = c.int_CustomerID and j.int_FoodgroupID = l.int_FoodgroupID
left join tblProduct p on p.int_ProductID = j.int_ProductID
where c.int_CustomerID = @customerID

Sample execution

exec uspGetCustomerProductSelections 1

Output

int_CustomerID int_FoodgroupID str_FoodgroupHandle  int_ProductID  str_ProductName
-------------- --------------- ---------------------------------------------------
1              1               fruit                1              apple
1              2               meat                 6              beef
1              3               bread                9              white
1              4               cheese               NULL           NULL

---

You should use outer joins (left, right or full). These joins will include null values.