开发者

Right join on each row

Is there an easy way to do the following without having to recur to temporary tables or cursors?

“Table 1 right join Table 2, but for each row in Table 1.”

Eg:

Table 1, Row 1 right join Table 2.  
Table 1, Row 2 right join Table 2.  

Etc.

Thanks

Update 1

Sorry I should of explained in a bit more detail.

Table definitions:

Table1:

TABLE [dbo].[Table_1]
(  
          [id] [int],  
          [name] [nvarchar](max) NULL,  
          [id_table2] [int] NULL)

Table 2.

TABLE [dbo].[Table_2]  
(  
[id] [int] NOT NULL,  
[code] [nvarchar](max) NULL,  )

Table 1 data:

Table 1 Data:  
1       Prov1    1  
2       Prov2    2  

NULL NULL NULL

Table 2 data:

Table 2 Data:  
1    01  
2    02  
3    03  
4    04  
5    05  

NULL NULL

If I do the following:

select * from Table_1 as t1  
right join Table_2 as t2 on  
t1.id_table2 =  
t2.id

result:

1       Prov1    1      1    01  
2       Prov2    2      2    02  
NULL    NULL    NULL    3    03  
NULL    NULL    NULL    4    04  
NULL    NULL    NULL    5    05

Result I'd like:

"Right join on each table 1 row"

1    Prov1    1       1   开发者_高级运维 01  
1    Prov1    NULL    2    02  
1    Prov1    NULL    3    03  
1    Prov1    NULL    4    04  
1    Prov1    NULL    5    05

2    Prov2    1       1    01  
2    Prov2    NULL    2    02  
2    Prov2    NULL    3    03  
2    Prov2    NULL    4    04  
2    Prov2    NULL    5    05


I think you should be looking for a CROSS JOIN instead of RIGHT JOIN.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜