Menu

#5 Union and Union All in the same SelectQuery

sqlbuilder-2.0.4
closed
sqlbuilder (11)
5
2012-09-29
2009-05-20
Anonymous
No

Hi,

I would like to create a SQLcontaining a UNION and UNION ALL in the same query
How i can do it with SqlBuilder?

So like that:
SELECT t0.A, t0.B FROM TABLE_A t0
UNION
SELECT t1.A, t1.B FROM TABLE_B t1
UNION ALL
SELECT t2.A, t2.B FROM TABLE_C t2

Discussion

  • James Ahlborn

    James Ahlborn - 2009-05-20

    Well, it's certainly not easy in the current API.

    is that even legal SQL?

     
  • Nobody/Anonymous

    Hi James,

    The SQL above is legal and it belongs the SQL ANSI 92 as well. I have tested this SQL in Oracle, MS SQLServer, Postgres, Intersystems Caché, MySQL and Hypersonic databases.

    Regards

     
  • Nobody/Anonymous

    Can i do it using the CustomSQL ?

     
  • James Ahlborn

    James Ahlborn - 2009-05-22

    Well, you can do anything you want with CustomSql. :)

    Using the current code, you could do something like (assuming you have q1, q2, q3 as your 3 queries):

    SqlObject obj = SqlObjectList.<SqlObject>create(UnionQuery.Type.UNION)
    .addObjects(q1, SqlObjectList.<SqlObject>create(UnionQuery.Type.UNION_ALL)
    .addObjects(q2, q3));

     
  • James Ahlborn

    James Ahlborn - 2009-05-22

    However, i'm planning on adding a method to UnionQuery so you could do something like:

    UnionQuery.union(q1, q2).addQueries(UnionQuery.Type.UNION_ALL, q3);

     
  • James Ahlborn

    James Ahlborn - 2009-05-22

    FYI, i've added this code to the trunk of SqlBuilder, it will be in the 2.0.5 release.

    I've also added support for the "intersect" and "except" operators as well (all of which can be mixed together).

     

Log in to post a comment.