CODEWITHSUNDEEP
sqlsql-servervariablescross-join
slayernoah
slayernoah

Reputation: 4492

SQL Cross Join Query not working with variable

This cross join works fine:

select * from x, y

I am trying to run this query:

select abc.col1 from abc
(select * from x, y) abc

But I get this error message:

Msg 8156, Level 16, State 1, Line 2
The column 'col1' was specified multiple times for 'abc'.

Both tables x and y have the same columns and column definitions.

Any ideas/suggestions?

Upvotes: 0

Views: 1672

Answers (3)

The Hill Boy
The Hill Boy

Reputation: 162

you have to specify column name in inner query section. something like this:

select abc.col1 from abc
(select x.col1,y.col1 from x, y) abc

Upvotes: 1

Lock
Lock

Reputation: 5522

select abc.col1 from abc
(select * from x, y) abc

You are aliasing two tables with the same name. Try:

select abc.col1 from abc,
(select x.* from x, y) abc2

Upvotes: 2

Sebas
Sebas

Reputation: 21542

In addition to Lock's answer, you also forgot the comma:

select 
    abc.col1 
from abc, (select * from x, y) abc2

Even better, using the ansi 1992 notation:

select 
    abc.col1 
from abc CROSS JOIN (select * from x, y) abc2

Upvotes: 0

Related Questions