LS

This is correct (resulting in 168 rows)

select * from regd
join gebr on gebr.id_gebruiker = regd.id_gebruiker
where id_team = 77

This is not I guess; because it misses 'gebr' after the keyword ON
(resulting in 32758 rows)

select * from regd
join gebr on id_gebruiker = regd.id_gebruiker
where id_team = 77

The team only has two id_gebruiker. If I use a team with hunderd
id_gebruiker then I get millions of records (that's how I discovered this

So, is this correct sql after all and gives a correct result (other then
what I expected), or should an error be expected?

Regards, Harry

Harry,

<< So, is this correct sql after all and gives a correct result (other then
what I expected), or should an error be expected? >>

Could you post the two table definitions in SQL ?

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

> Could you post the two table definitions in SQL ?

Regards, Harry

EXECUTE IMMEDIATE 'CREATE TABLE "GEBR"
                    (
                    "Id_Gebruiker" INTEGER NOT NULL,
                    "Id_Team" INTEGER NOT NULL,
                    "Naam_Gebruiker" VARCHAR(30) COLLATE "ANSI" NOT NULL,
                    "Dienstverband" VARCHAR(2) COLLATE "ANSI",
                    "Id_Vestiging" INTEGER NOT NULL,
                    "Id_Werkgever" INTEGER NOT NULL,
                    "Min_Uren" SMALLINT NOT NULL,
                    "Max_Uren" SMALLINT,
                    "Datum_Geboorte" DATE,
                    "Geslacht" VARCHAR(1) COLLATE "ANSI",
                    "Password" VARCHAR(10) COLLATE "ANSI",
                    "Aut_Registreren" BOOLEAN,
                    "Aut_Accorderen" BOOLEAN,
                    "Aut_RegistrerenPlus" BOOLEAN,
                    "Aut_Corrigeren" BOOLEAN,
                    "Aut_Periode_Afsluiten" BOOLEAN,
                    "Aut_Klant_Proj_Camp_Uurcode" BOOLEAN,
                    "Aut_Team_Gebruiker" BOOLEAN,
                    "Afwezig_Ziek" BOOLEAN,
                    "Afwezig_Overig" BOOLEAN,
                    "SoFiNummer" INTEGER,
                    CONSTRAINT "PrimaryKey" PRIMARY KEY ("Id_Gebruiker")
                    )
                    VERSION 1
                    UNENCRYPTED
                    INDEX PAGE SIZE 4096
                    BLOB BLOCK SIZE 512
                    MAX ROW BUFFER SIZE 32768
                    MAX INDEX BUFFER SIZE 65536
                    MAX BLOB BUFFER SIZE 32768';

  EXECUTE IMMEDIATE 'CREATE INDEX "idxTeam" ON "GEBR"
                    ("Id_Team")';

 EXECUTE IMMEDIATE 'CREATE TABLE "REGD"
                    (
                    "Id_gebruiker" INTEGER NOT NULL,
                    "Datum" DATE NOT NULL,
                    "Tijd_begin" TIME NOT NULL,
                    "Tijd_einde" TIME,
                    "Geaccordeerd" BOOLEAN,
                    "Afgesloten" BOOLEAN,
                    CONSTRAINT "PrimaryKey" PRIMARY KEY ("Id_gebruiker",
"Datum")
                    )
                    DESCRIPTION ''Registratie dag''
                    VERSION 1
                    UNENCRYPTED
                    INDEX PAGE SIZE 4096
                    BLOB BLOCK SIZE 512
                    MAX ROW BUFFER SIZE 32768
                    MAX INDEX BUFFER SIZE 65536
                    MAX BLOB BUFFER SIZE 32768';



"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht
news:96533EBC-20F5-421B-A343-BE6A93173387@news.elevatesoft.com...
> Harry,
>
> << So, is this correct sql after all and gives a correct result (other
then
> what I expected), or should an error be expected? >>
>
> Could you post the two table definitions in SQL ?
>
> Thanks,
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Harry,

Thanks.  I actually could have answered the question without them, but I was
a little busy yesterday and didn't have my head straight.

Because you're using the REGD table as the first table in the query, any
columns without an explicit table qualifier name will use the REGD table as
the table to first look for the column.  So, basically your query ends up
doing a join on the same column in the same table.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< Because you're using the REGD table as the first table in the query, any
columns without an explicit table qualifier name will use the REGD table as
the table to first look for the column.  >>

The correct SQL behavior would be to raise an "ambiguous column reference"
exception.

Ole Willy Tuv

Ole,

<< The correct SQL behavior would be to raise an "ambiguous column
reference" exception. >>

Yes, I understand.  I still have that on my to-do list, believe it or not,
along with the GROUP BY warning, etc.  I really need to go ahead and get
those in EDB.

--
Tim Young
Elevate Software
www.elevatesoft.com