What type Join to Use?

[newone]

I have two tables that I want to join. I have common fields in both tables that I would like to join by. However, both tables contain some data that the other table does not have a match for. How do I join so that I receive ALL values in both tables even if there is no match? I am aware that I can left join and get all data from left table and vice versa for the right table. However, I need data from both tables regardless of whether there is a match. How do I do this?

Table A has two fields: AcctID and ActualRevenue. TableB has two fields: AcctID and ProjectedRevenue. Table B contains customers who don't yet use our product but will in the future. For budget purposes I need a query that will give me the actual revenue as well as projected revenue for a date in the future. Obviously, the AcctID for the ProjectedRevenue is not in Table A because we don't have any actual revenue at this point. Once we have revenue from them, this won't be a problem. How do I join these two tables to match all records as well as return records for both tables that do not match?

 

[directormac]

Hmmm... try this SQL query:

SELECT AcctID, ActualRevenue, 0 AS ProjectedRevenue FROM TableA
UNION SELECT AcctID, 0 AS ActualRevenue, ProjectedRevenue FROM TableB ORDER BY AcctID;

 

[newone]

That worked perfect!! Thanks!

I had tried the Union but was missing the "0 As ProjectedRevenue" part. Once I added that, it's exactly like I needed.

Thanks again.