Joining Two tables

Hi
Can anyone help me with a query two join two tables.

I have

Bud

Month code Budget
7 465318 300
9 465318 300


Fore

Month code Forecast
5 465318 400
6 465318 100
7 465318 200

I need to combine both tables (and in this case selecting month 5 from the Fore table) however in some cases there will be a bud without a Fore and visa versa

In the example above I want the result to be.

Month code Budget Forecast
7 465318 300 400
9 465318 300 400


I have tried everything.

The problem is when there is no month 5 (my selected month) in Fore. I still need it to show the data in the Bud for that code.

Hope this makes sense.

Thanks

Hi Andy,

You require a full Join, I rarely use them, but I believe this is what it should look like.

SELECT Fore.Month, Fore.code, Fore.Forecast, Bud.Budget
FROM Fore
FULL JOIN Bud
ON Fore.Month=Bud.Month and Fore.code=Bud.code

Im relativly new to this, but hope that helps

Thanks for your reply it worked a treat !!

However I now have another problem.

This works fine if I don't use any criteria.

However I want to be able to select a month from each table (the month may be different) for example month 7 in the table Bud and month 5 in table Fore

If I use where Bud = 7 and Fore = 5 it only gives me data if there is data for month 7 and month 5.

I want it to show null if no data in one of the tables and the correct data in the other.

Hi Andy,

I dont see that working, basically the query makes the database believe that the month wether it be for bud or fore are now the same.

So you would need to use:

Where bud.month =7 OR bud.month =5

You could also use

Where fore.month =7 OR fore.month =5

If combined with the rest of the query, both those lines will return the same results.

-Kenny

Hi Kenny

Thanks for the fast reply.

If I used 'or' I would also get month 5 data for Bud and month 7 data for Fore which I don't want.

I could create two views that would select the relevant month for each table and join them in an other.

However I don't want to hard code the month as I plan to link the view to excel and get the user to select their required months.

Hi Andy,

There is an alternative way to presenting this data, I'd mentioned earlier that I don't regularly use full joins. This is because I use a Union instead.

Basically if you have a field within both tables that distinguishes it as either bud or fore you could use the following

Select fore.month, fore.code, fore.forecast, fore."Unique"
Union all
Select bud.month, bud.code, bud.dudget, bud."Unique"

This will then display

Month Code Fore/bud "Unique"
7 475832 £200 Forecast
7 475832 £210 Budget
8 695832 £300 Forecast
8 695832 £350 Budget
9 475832 £220 Forecast
12 475832 £371 Budget

I dont know how your data tables are set out, but Im sure there must be a field that has a 0 in bud and a 1 in fore or maybe a letter or something else that you could use to identify which one it is.

For me, this enables me to use pivot tables, or summary sheets to better display the data as all the data is in a list.

-Kenny

mis-post