cross join of 2 unions

[ericjs]

I want to do a query where I end up cross joining the results of 2 unions. While union operates on two select statements, cross join operates within a select statement, and I'm at a loss for how to reconcile the syntax.


To avoid going into the complexities of my queries which are irrelevant to my question, let me give a very simple example, of having a query like

select 'a', 'b' union select 'aa', 'bb'

which I wish to cross join with

select 'c', 'd' union select 'cc', 'dd'

I can solve this by turning each of my two union queries into a view and then do a cross join on the 2 views. However it seems silly to have to do this, so I thought I would throw this out there in case anyone knows how to make this all work as single query.

Thanks,

Eric

 

[JarlH]

Have you tried:
[tt]
select * from (select 'a', 'b' union select 'aa', 'bb')
cross join
(select 'c', 'd' union select 'cc', 'dd')
[/tt]

 

[ericjs]

JarlH:

I've tried various syntax variations at least very much like that, but what you posted gave me the most helpful error I've seen yet (in PostgreSQL 8.4): "subquery in FROM must have an alias".
Following that clue, I find the following actually works:

select * from (select 'a', 'b' union select 'aa', 'bb') as x cross join(select 'c', 'd' union select 'cc', 'dd') as y

The other RDBMS I have handy to try, MS SQL Server 2005, is having none of it. Your query gives

"Incorrect syntax near the keyword 'cross'"

and the syntax that works in PostgresSQL gives multiple errors indicating it doesn't understand the aliasing.

Out of curiousity, did your syntax work as you posted it on some particular system?

In any case, thanks much!

Eric

 

[Aluminum]

IN SQL SERVER you need aliases

select * from (select 'a' a, 'b' b union select 'aa' a, 'bb' b) a
cross join
(select 'c' a, 'd' b union select 'cc' a, 'dd' b) b

 

[ericjs]

Aluminum,

I need the table aliases in PostgreSQL too, that's what the AS in my query is for. PostgreSQL also works with the syntax you posted, without the AS. Apparently SQL Server doesn't recognize the AS. Just out of curiousity, anyone know which is or isn't ANSI standard?

Eric

 

[JarlH]

In ANSI SQL you have to put tables in SELECT's. I.e. you are lost already here:
[tt]select 'a', 'b' union select 'aa', 'bb'
^----
syntax error: union
correction: . * FROM <identifier> union
[/tt]

Check out the SQL Validator:

http://developer.mimer.com/validator/

 

[ericjs]

JarlH,

Every RDBMS I've ever used allows literals in SELECT statements and makes the FROM clause optional. For example
"SELECT 1" is very commonly used as a "validation query" to test that the connection is still valid. Are you saying that this is actually not provided for by the ANSI spec? What RDBMS is it that gives you that error?

In any case, this example I used is nothing like my real query, I simply used a tableless, literals-only query for my example to make it as simple as possible and avoid having to posit a schema.

 

[JarlH]

The output is from the SQL Validator, an online tool which is used to verify the SQL syntax' standard compliance. A great time saver when writing portable code!