Interesting Query, ... I Supose

[MarKes]

Hi
I have two tables:

Code Month
---- ------
1 January
2 February
. ...
12 December

CodeMonth Year Data
--------- ---- ----
1 2000 12
2 2000 23
1 2001 35
2 2001 19
3 2001 15
. ... ...
12 2001 25

As you can see, there are months un a year that doesn´t have data, but I need to show all months of all Years (whit null, blank...), something like this:

year Month(Literal) Data
---- -------------- ----
2000 January 12
2000 February 23
2000 March NULL
2000 April NULL
... ... ...
2000 December NULL
2001 January 35
2001 February 19
2001 March 15
2001 April NULL
2001 May NULL
... ... ...
2001 December 25

well, I know that it shows single but I don´t know how to make the query (queries) necesary...

thanks in advance
(Soy español, Si te resulta mas facil puedes contestar en español...)

 

[TheInsider]

Try this:

SELECT Table2.Year, Table1.Month, Table2.Data
FROM Table1 INNER JOIN Table2 ON Table1.Code = Table2.CodeMonth;

 

[jjrockman]

I somewhat disagree with the solution that CCTC1 provided...indeed his/her query does work for providing returned rows for the months that you have data for, but a query itself cannot make up for the data that is missing. For example, if you are missing one month from the year 2000, no query using those two tables will return that missing month. My suggestion to you is possibly to change your Code/Month table to include a year column as well.
So, the table will look like this:

Code Month Year
---- ----- ----
1 January 1999
2 February 1999
. .... ...
12 December 1999
13 January 2000
14 February 2000

Then, you can shorten your CodeMonth/Year/Data table to just CodeMonth/Data. Since each CodeMonth refers to a single Month/Year combination, you can use a query like this:

select codes.year, codes.month, data.data
from code LEFT OUTER JOIN data on codes.code = data.codemonth

and this will return the same number of rows as you have in your codes table.

 

[TheInsider]

oops... I wasn't fully paying attention to what MarKes was trying to do.