Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

cross join of 2 unions

Status
Not open for further replies.
ericjs

ericjs

Programmer
Mar 30, 2010
4
US
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
 
Sort by date Sort by votes
Have you tried:
[tt]
select * from (select 'a', 'b' union select 'aa', 'bb')
cross join
(select 'c', 'd' union select 'cc', 'dd')
[/tt]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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:
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BobThornton
  • Locked
  • Question
Cross Join in an Insert Into
Replies
1
Views
380
carp
carp
sal21
  • Locked
  • Question
SORT by part of filed 2
Replies
5
Views
511
2009luca
2009luca
Tetol
  • Locked
  • Question
Update script is changing the number of records for the values updated each time I run the query
Replies
1
Views
379
carp
carp
ahadali79
  • Locked
  • Question
Getting a part of string
Replies
2
Views
288
johnherman
johnherman
SashaBuilder3
  • Locked
  • Question
left join with &quot;soft&quot; condition
Replies
2
Views
258
SashaBuilder3
SashaBuilder3

Part and Inventory Search

Sponsor

Back
Top