cross join of 2 unions
cross join of 2 unions
(OP)
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
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
Talk To Other Members
RE: cross join of 2 unions
select * from (select 'a', 'b' union select 'aa', 'bb')
cross join
(select 'c', 'd' union select 'cc', 'dd')
RE: cross join of 2 unions
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
RE: cross join of 2 unions
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
RE: cross join of 2 unions
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
RE: cross join of 2 unions
select 'a', 'b' union select 'aa', 'bb'
^----
syntax error: union
correction: . * FROM <identifier> union
Check out the SQL Validator:
http://developer.mimer.com/validator/
RE: cross join of 2 unions
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.
RE: cross join of 2 unions