Re: Base:(H)SQL Join, strange things with table designer

Hi Wolfgang,

Does this also happen on other databases? I just stumble about issue 
http://sourceforge.net/tracker/index.php?func=detail&aid=1344316&group_id=23316&atid=378131
in hsqldb. May be this is the same problem?


-- OJ

Wolfgang Schaible wrote:
> Hi,
> here my Tables (and some of my problems):
> +- - - +                +- - - +
> + JM   +                + JS   +
> +- - - +      IJ        +- - - +
> | MID* | - - - - - - - -| MID  |
> |      |   1       n    |      |
> |      |                +- - - +
> |      |
> |      |                +- - - +
> |      |                + REC  +             +- - - + Usage:
> |      |       RJ       +- - - +             + ADR  + Read only
> | RID  | - - - - - - - -| RID* |      IJ     +- - - + extern to
> |      |  0|1      1    | AID  |- - - - - - -| AID* | applic
> +- - - +                +- - --+   1     1   |NAME  |
>                                              +- - - +
>
> 1. Using only the 3 tables Jm, JS, REC the SQL statement produced 
> with the designer is ok, also the query output. 
> 2. Bad: no labels allowed (SELECT "JM" AS "M" ...), gives bulky 
> statements with the real (long) table names
> 3. Inserting ADR (has 3 records) gives 3 times more output. hmm, 
> without any connections? a M$-default?
> 4. Connecting now REC.AID with ADR.AID using Inner (or left, right) 
> Join presents a empty table, not was I expected :-((
> The statement produced ignores, as I can see with my little SQL 
> experience, all other joins but REC-ADR:
> SELECT "JM"."*", "JS"."*", "ADR"."*"
> FROM { OJ "REC" "REC" RIGHT OUTER JOIN "JM" "JM" 
> ON "REC"."RID"= "JM"."RID" },
>       "JS" "JS", "ADR" "ADR" 
> WHERE ( "JS"."MID" = "JM"."MID" AND "ADR"."AID" = "REC"."AID" ) 
> ORDER BY "JS"."DATEV" ASC, "JS"."VAL" DESC
>
> BTW
> Setting Constraint Foreign Key MID in JS was accepted, but RID in JM 
> not... No idea
>
> All my tries to write SQL with better results are ending in error 
> msg's. I am not shure is this my fault or are this to many 
> constraints to HSQLDB?
>
> Any idea?
>
> Wolfgang
>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
>   


-- 
Ocke Janssen                      Tel: +49 40 23646 661, x66661
Dipl. Inf(FH)                     Fax: +49 40 23646 550
Sun Microsystems Inc.
Nagelsweg 55                     mailto:[email protected]
D-20097 Hamburg                   http://www.sun.com/staroffice

			Example isn't another way to teach, 
			it is the only way to teach.  
			Albert Einstein