SQL Cross Join Query not working with variable

Question

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?

Lock · Accepted Answer · 2013-11-06T03:15:41.473

2

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

edited Nov 06 '13 at 03:15

answered Nov 06 '13 at 03:10

Lock

Thanks for the answer. I am trying to put `(select * from x, y)` as a variable `abc` and then use that variable in the select statement `select abc.col1 from abc` – slayernoah Nov 06 '13 at 03:12
Are there any columns in tables `x` and `y` that have the same name? If so, you will need to prefix with the table name such as `(select x.* from x, y)`. See my update. – Lock Nov 06 '13 at 03:13

score 1 · Answer 2 · answered Nov 06 '13 at 05:52

1

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

answered Nov 06 '13 at 05:52

The Hill Boy

score 0 · Answer 3 · answered Nov 06 '13 at 03:12

0

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

answered Nov 06 '13 at 03:12

Sebas

3 Answers3