×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

cross join of 2 unions

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

RE: cross join of 2 unions

Have you tried:

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

RE: cross join of 2 unions

(OP)
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

RE: cross join of 2 unions

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

RE: cross join of 2 unions

(OP)
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

RE: cross join of 2 unions

In ANSI SQL you have to put tables in SELECT's. I.e. you are lost already here:
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

(OP)
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.

RE: cross join of 2 unions

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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close