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.
ThanksUpdate 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:
1 01
2 02
3 03
4 04
5 05
NULL
NULL
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.
精彩评论