>Hi all, is there a way to select records from two tables where the right hand side table may or may not have a matching record ? What I'm trying to do is produce a yearly report of sales per month per salesperson and if there are no entries for a given month I still want a record for the salesperson but showing a zero amount. I've looked at various *solutions* on the web but they all produce a null set which is not what I want. Null values for the sales is acceptable but I always want the salesperson name returned
>
>

>// Table 1 ( Salesperson )
>Salesperson
>Pete
>Paul
>Mary
>
>// Table2 ( Sales )
>Salesperson.   Sale date.               SaleAmount
>Pete.                  01/01/2015.          1000
>Pete.                  01/01/2015.           2000
>
>I would like the report to show
>
>Salesperson.  Month.   Sumofmonthsales
>Pete                  Jan.        3000
>Pete.                  Feb.        0
>

>
>
>etc... all through to December, and ditto for the rest of the salespersons. Hope this makes sense ( sorry about the formatting )

DECLARE @Months TABLE (Mnt tinyint)
INSERT INTO @Months VALUES (1)
INSERT INTO @Months VALUES (2)
INSERT INTO @Months VALUES (3)
INSERT INTO @Months VALUES (4)
INSERT INTO @Months VALUES (5)
INSERT INTO @Months VALUES (6)
INSERT INTO @Months VALUES (7)
INSERT INTO @Months VALUES (8)
INSERT INTO @Months VALUES (9)
INSERT INTO @Months VALUES (10)
INSERT INTO @Months VALUES (11)
INSERT INTO @Months VALUES (12)

/* Preparing example data */
DECLARE @Salesperson TABLE (Salesperson char(4))
INSERT INTO @Salesperson VALUES ('Pete')
INSERT INTO @Salesperson VALUES ('Paul')
INSERT INTO @Salesperson VALUES ('Mary')

DECLARE @Sales TABLE (Salesperson char(4), SaleDate datetime, SaleAmount int)
INSERT INTO @Sales VALUES('Pete', '20150101', 1000)
INSERT INTO @Sales VALUES('Pete', '20150101', 2000)
/*End preparing example data */

SELECT Prs.Salesperson,
       Prs.Mnt,
       COALESCE(Tbl1.Sum, 0) AS Sumofmonthsales
FROM (SELECT Salesperson.Salesperson,
             Mnt.Mnt
      FROM @Salesperson Salesperson
      CROSS JOIN @Months Mnt) Prs
LEFT JOIN (SELECT Salesperson,
                  MONTH(SaleDate) AS Mnt,
                  SUM(SaleAmount) AS Sum
           FROM @Sales Sales
           WHERE YEAR(SaleDate) = 2015
           GROUP BY Salesperson, MONTH(SaleDate)) Tbl1
      ON Prs.Salesperson = Tbl1.Salesperson AND
         Prs.Mnt = Tbl1.Mnt
ORDER BY Prs.Salesperson,
         Prs.Mnt