Hi Fred,
I have used the DatabaseManager, it is great.
I have solved my problem:
CREATE TABLE "groupmembership" ("id" INT NOT NULL, "user" INT NOT NULL,
"group" INT NOT NULL)
CREATE TABLE "groupcapability" ("id" INT NOT NULL, "group" INT NOT NULL,
"capability" INT NOT NULL)
SELECT * FROM "groupmembership" WHERE "user" = 1 AND
EXISTS ( SELECT "groupcapability"."group"
FROM "groupcapability", "groupmembership"
WHERE "groupcapability"."group" = "groupmembership"."group"
AND "capability" = 0);
fails where
SELECT "groupcapability"."group"
FROM "groupcapability", "groupmembership"
WHERE "groupcapability"."group" = "groupmembership"."group"
AND "capability" = 0;
suceeds
and
SELECT * FROM "groupmembership" WHERE "user" = 1
suceeds
finally realised that it is a scoping problem as
SELECT * FROM "groupmembership" WHERE "user" = 1 AND
EXISTS ( SELECT "groupcapability"."group"
FROM "groupcapability", "groupmembership" as G2
WHERE "groupcapability"."group" = G2."group"
AND "capability" = 0);
suceeds.
thanks very much for your help,
I will continue to test and let you know when I
make the next release.
cheers
Tim P
On Saturday 25 May 2002 18:38, fredt wrote:
> 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
