Confused by a union SQL query

[shaunacol]

I have 2 queries. One is called NumberOfOrders and lists the CustomerID, Month and NoOfOrders for that month. I also have another query called NumberOfQuotes which lists the CustomerID, CustomerName (because not all new customers who have quotes will get an ID), Month and NumberOfQuotes for that month.

I would like to join the 2 queries by Customer ID and by month so that we can see the number of quotes AND the number of orders for each customer per month side by side. The other complication is that I want to see the number of quotes even for a customer who does not have a customerID. The query below seems to join the tables but only brings back information if the customer has data for orders AND quotes in that month and not if they only have data for one or the other. Please help!


SELECT NumberOfOrders.NoOfOrders, NumberOfQuotes, NumberofOrders.Month, CustID, CustomerName
FROM NumberOfQuotes INNER JOIN NumberOfOrders ON (NumberOfOrders.month=NumberOFQuotes.month) AND (NumberOfOrders.CustomerID=NumberOFQuotes.custID);

 

[djj55]

If all Customer ID values in NumberOfOrders are in NumberOfQuotes use a Left or Right join. (depending on where NumberOfQuotes is)

If not you will need third query that is a UNION to get a list of all Customer ID/Customer Names. Then a Left or Right join from there

djj

 

[shaunacol]

Thanks! That second option souinds like it might work....l will try it tonight!