How to join two tables without a common column to join on

Question

How to join two tables without a common column to join on

NineIron

SSChampion

Points: 12536
More actions
October 25, 2013 at 8:06 am

#281705

I've got two CTEs. CTE1 returns one record. CTE2 returns 12. Can I join the two so that, for each record in CTE2, it returns the record from CTE1?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

NineIron (10/25/2013)
I've got two CTEs. CTE1 returns one record. CTE2 returns 12. Can I join the two so that, for each record in CTE2, it returns the record from CTE1?

Use a cross join.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 2

NineIron (10/25/2013)
I've got two CTEs. CTE1 returns one record. CTE2 returns 12. Can I join the two so that, for each record in CTE2, it returns the record from CTE1?

try this:

declare @table1 table (data1 int)

declare @table2 table (data2 int)

insert into @table1 values (1)

insert into @table2 values (1)

insert into @table2 values (2)

insert into @table2 values (3)

insert into @table2 values (4)

insert into @table2 values (5)

insert into @table2 values (6)

insert into @table2 values (7)

insert into @table2 values (8)

insert into @table2 values (9)

insert into @table2 values (10)

insert into @table2 values (11)

insert into @table2 values (12)

;with table1_cte (data1) as (

select data1

from @table1

), table2_cte (data2) as (

select

data2

from @table2

)

select *

from table1_cte, table2_cte

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

NineIron SSChampion Points: 12536 More actions · Answer 3

NineIron

SSChampion

Points: 12536

October 25, 2013 at 8:19 am

#1661645

Thanx.

NineIron SSChampion Points: 12536 More actions · Answer 4

Thanx.

Both solutions work fine. I'm going with cross join.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

NineIron (10/25/2013)
Thanx.
Both solutions work fine. I'm going with cross join.

Actually they are both cross joins. I prefer to be explicit when using a cross join instead of the shortcut of table1, table2.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/