Vfrdtyky

Somebody has to know why this happens...

The first two scripts are almost exactly the same, the only difference is that one is a left join and the other one is an inner join. But they both return the same exact rows:

declare @calendar table 

(

    monthNum int

)

insert into @calendar

select 1 union

select 2 union

select 3 union

select 4



declare @whatjoins table

(

    id varchar(3),

    monthnum int,

    total int

)

insert into @whatjoins 

select 'aaa', 1, 400 union 

select 'aaa', 2, 400 union 

select 'aaa', 3, 400 union 

select 'aaa', 4, 400 union 

select 'bbb', 1, 500 union 

select 'bbb', 3, 500 union 

select 'bbb', 4, 999 union

select 'ccc', 1, 999 union 

select 'ccc', 2, 999 union 

select 'ccc', 4, 999





select 

c.monthnum, w.id, w.monthnum, w.total

from @calendar c inner join @whatjoins w on

c.monthNum = w.monthnum

order by c.monthNum, w.monthnum, id

The same script with a left join, returns the same number of rows:

declare @calendar table 

(

    monthNum int

)

insert into @calendar

select 1 union

select 2 union

select 3 union

select 4



declare @whatjoins table

(

    id varchar(3),

    monthnum int,

    total int

)

insert into @whatjoins 

select 'aaa', 1, 400 union 

select 'aaa', 2, 400 union 

select 'aaa', 3, 400 union 

select 'aaa', 4, 400 union 

select 'bbb', 1, 500 union 

select 'bbb', 3, 500 union 

select 'bbb', 4, 999 union

select 'ccc', 1, 999 union 

select 'ccc', 2, 999 union 

select 'ccc', 4, 999





select 

c.monthnum, w.id, w.monthnum, w.total

from @calendar c left join @whatjoins w on

c.monthNum = w.monthnum

order by c.monthNum, w.monthnum, id

The next, and 3rd, script, correctly displays the left join. The only difference between this script and the previous one is that I commented the inserts so that @whatjoins only has 'ccc'.

declare @calendar table 

(

    monthNum int

)

insert into @calendar

select 1 union

select 2 union

select 3 union

select 4



declare @whatjoins table

(

    id varchar(3),

    monthnum int,

    total int

)

insert into @whatjoins 

--select 'aaa', 1, 400 union 

--select 'aaa', 2, 400 union 

--select 'aaa', 3, 400 union 

--select 'aaa', 4, 400 union 

--select 'bbb', 1, 500 union 

--select 'bbb', 3, 500 union 

--select 'bbb', 4, 999 union

select 'ccc', 1, 999 union 

select 'ccc', 2, 999 union 

select 'ccc', 4, 999





select 

c.monthnum, w.id, w.monthnum, w.total

from @calendar c left join @whatjoins w on

c.monthNum = w.monthnum

order by c.monthNum, w.monthnum, id

My main question: why are the first 2 scripts returning the same data?

My secondary question: is there some similar way to use the left join that will return all 12 rows (10 matches and 2 rows with null)?

This is the result I'm looking for:

1   bbb     1       500

2   NULL    NULL    NULL

3   bbb     3       500

4   bbb     4       999

1   ccc     1       999

2   ccc     2       999

3   NULL    NULL    NULL

4   ccc     4       999

1   aaa     1       400

2   aaa     2       400

3   aaa     3       400

4   aaa     4       400

Not an efficient one, but works.

declare @calendar table 

(

    monthNum int

)

insert into @calendar

select 1 union

select 2 union

select 3 union

select 4



declare @whatjoins table

(

    id varchar(3),

    monthnum int,

    total int

)

insert into @whatjoins 

select 'aaa', 1, 400 union 

select 'aaa', 2, 400 union 

select 'aaa', 3, 400 union 

select 'aaa', 4, 400 union 

select 'bbb', 1, 500 union 

select 'bbb', 3, 500 union 

select 'bbb', 4, 999 union

select 'ccc', 1, 999 union 

select 'ccc', 2, 999 union 

select 'ccc', 4, 999





select 

c.monthNum,w2.*

from @calendar c CROSS JOIN (SELECT DISTINCT id FROM @whatjoins w)w

LEFT JOIN @whatjoins w2 ON w2.id = w.id AND c.monthNum=w2.monthnum

ORDER BY id

Only thing is I can't figure out an order by for this for the exact output you asked

ORDER BY ROW_NUMBER() OVER(PARTITION BY c.monthNum ORDER BY c.monthNum)

this is something I have come across but still not valid

You need CROSS JOIN before LEFT JOIN because of you want to repeat all monthnums for each ids :

select distinct c.monthNum, w.id, w1.monthnum, w1.total 

from @whatjoins w cross join

     @calendar c left join

     @whatjoins w1 

     on w1.id = w.id and w1.monthnum = c.monthNum

order by w.id, c.monthNum;