Re: [JBoss-user] Entity Bean Performance Tuning Help (intersection)

[Dain Sundstrom]

That shouldn't be to hard once we add support for sub queries, but unless it is supported by most of the major vendors (postgres, oracle, ms, ibm), it wouldn't be worth the work.

-dain

[EMAIL PROTECTED] wrote:

On Tue, Nov 12, 2002 at 04:53:28PM -0600, Dain Sundstrom wrote:

What is an INTERSECTION query?

From postgresql-help:

Syntax:
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
*** [ { UNION | INTERSECT | EXCEPT [ ALL ] } select ] ***
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ FOR UPDATE [ OF tablename [, ...] ] ]
[ LIMIT { count | ALL } [ { OFFSET | , } start ]]

I think it is used like:
SELECT personID from persondata where datatype=1 AND value="ln1"
INTERSECT
SELECT personID from persondata where datatype=2 AND value="ln2"

finds the persons registered with ln1 as datatype 1 and ln2 as datatype 2.

I think the obvious solution "SELECT personID from persondata where (datatype=1 AND value=ln1") AND (datatype=2 AND value="ln2") doesn't work, as it compares one and one row.
In this table, personID isn't unique.

Is it widely supported?

I suppose so in the major DBs, if it isn't a simpler solution to this problem (using just one SQL statement).

In addition UNION and EXCEPT should be implemented (if these are not possible to work around).

-------------------------------------------------------
This sf.net email is sponsored by: Are you worried about your web server security? Click here for a FREE Thawte Apache SSL Guide and answer your Apache SSL security needs: http://www.gothawte.com/rd523.html
_______________________________________________
JBoss-user mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-user