I thinkt a right outer join would fix this but

I am a SQL newbie but have leaned enough to get in trouble.

I have a table called Sales containing naturally fields relating to a sale including a field called Salesman.

I also have another table called Salesmen containing just the salesmen names.

I want a report that counts all the sales for every s开发者_JS百科alesman including a result for each salesman who is not mentioned in the Sales table due to lack of sales.

The following code works except the non performing salesman do not show, just the ones who have actually sold something.

SELECT salesman,
       count(*) as nmbr
  FROM Sales
        JOIN Salesmen
              ON Sales.salesman = Salesmen.name
 GROUP BY Salesmen.name
 order by nmbr;

I believe a Right Outer Join would fix this except I'm using SQLITE which doesn't allow this.

Any ideas on a workaround so I can find out which salesman haven't sold anything?

Does SQLLite support a LEFT outer join?

 SELECT salesmen.name
        count(sales.salesman) as nmbr
 FROM Salesmen
    LEFT JOIN Sales ON Sales.salesman = Salesmen.name
 GROUP BY Salesmen.name
 ORDER BY nmbr;

According to SQLite, you can do LEFT OUTER JOIN but not RIGHT OUTER JOIN. So how about just reversing the order of the tables in your query, and doing a LEFTy?