I created two tables that match the names and columns of your query and it works. The table creation DDL is as follows:
CREATE TABLE "groupmembership"("user" INTEGER, "group" INTEGER); CREATE TABLE "groupcapability"("group" INTEGER, "capability" INTEGER); It is obvious that one or both the tables were not defined with quoted names, in which case their names would be interpreted by any SQL92 compliant DB as all uppercase and would not match the quoted lowercase names. Try: SELECT * FROM groupmembership WHERE "user" = 1 AND EXISTS ( SELECT groupcapability."group" FROM groupcapability, "groupmembership" WHERE groupcapability."group" = groupmembership."group" AND "capability" = 0) If you want to see the exact DDL as interpreted by HSQLDB, run DatabaseManager, open the database, and issue the SCRIPT command. All unquoted names will be unquoted in uppercase and all quoted names will be quoted as they were defined. Fred ----- Original Message ----- From: "Tim Pizey" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: 25 May 2002 19:35 Subject: [Hsqldb-developers] Quoting problem Hi Fred, Here is another little one, I have quoted everything and come up with this SELECT * FROM "groupmembership" WHERE "user" = 1 AND EXISTS ( SELECT "groupcapability"."group" FROM "groupcapability", "groupmembership" WHERE "groupcapability"."group" = "groupmembership"."group" AND "capability" = 0) Error reported by jdbc driver Column not found: S0022 Column not found: group in statement [SELECT * FROM "groupmembership" WHERE "user" = 1 AND EXISTS ( SELECT "groupcapability"."group" FROM "groupcapability", "groupmembership" WHERE "groupcapability"."group" = "groupmembership"."group" AND "capability" = 0)] Is there an obvious way around this problem? cheers timp On Saturday 25 May 2002 17:22, Fred Toussi wrote: > Thanks Tim, > > We have made a big effort to extend support for SQL and JDBC standards as > well as improvements in other areas so that HSQLDB can be used with other > products with fewer problems. Please let us know about any other potential > issues. > > Fred _______________________________________________________________ Don't miss the 2002 Sprint PCS Application Developer's Conference August 25-28 in Las Vegas -- http://devcon.sprintpcs.com/adp/index.cfm _______________________________________________ hsqldb-developers mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/hsqldb-developers _______________________________________________________________ Don't miss the 2002 Sprint PCS Application Developer's Conference August 25-28 in Las Vegas -- http://devcon.sprintpcs.com/adp/index.cfm _______________________________________________ hsqldb-developers mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/hsqldb-developers