db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ян Программист <webautoma...@gmail.com>
Subject SQL again
Date Mon, 12 Apr 2010 14:14:11 GMT
I have finally found out that my understanding of JOIN in MySQL is not yet
other one that should be in Derby:

select SYS.SYSCONSTRAINTS.*, SYS.SYSCOLUMNS.COLUMNNAME from
SYS.SYSCONSTRAINTS right outer join SYS.SYSCOLUMNS on
SYS.SYSCONSTRAINTS.TABLEID = SYS.SYSCOLUMNS.REFERENCEID;

I wanted to force L-/R- JOINs to properly be interpreted. And got this:

CONSTRAINTID                        |TABLEID
|CONSTRAINTNAME
                                   |TYPE            |SCHEMAID
             |STA&     |REFERENCEC&|COLUMNNAME


----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
... ... ...
NULL                                          |NULL
       |NULL
                                                     |NULL
|NULL                                |NUL     L|NULL       |ISDEF


cbf5809d-0127-df6f-bee8-000000547e10|2ba7c09b-0127-df6f-bee8-000000547e10|SQL100409011100670

               |P   |80000000-00d2-b38f-4cda-000a0a412c00|E   |1
 |ID

cbf5809d-0127-df6f-bee8-0000005|2ba7c09b-0127-df6f-bee8-000000547e10|SQL100409011100670

                    |P   |80000000-00d2-b38f-4cda-000a0a412c00|E   |1
   |NAME

e50d80a4-0127-df6f-bee8-000000547e102-0127-df6f-bee8-000000547e10|SQL100409011755320

                       |P   |80000000-00d2-b38f-4cda-000a0a412c00|E   |0
     |RECORD_ID

4d3680a5-0127-df6f-bee8-000000547e10|94bc40af6f-bee8-000000547e10|SQL100409011755321

                       |F   |80000000-00d2-b38f-4cda-000a0a412c00|E   |0
     |RECORD_ID

e50d80a4-0127-df6f-bee8-000000547e10|94bc40a2-0127-d-000000547e10|SQL100409011755320

                       |P   |80000000-00d2-b38f-4cda-000a0a412c00|E   |0
     |SHUTDOWN_TYPE

4d3680a5-0127-df6f-bee8-000000547e10|94bc40a2-0127-df6f-bee847e10|SQL100409011755321

                       |F   |80000000-00d2-b38f-4cda-000a0a412c00|E   |0
     |SHUTDOWN_TYPE

e50d80a4-0127-df6f-bee8-000000547e10|94bc40a2-0127-df6f-bee8-0000005L100409011755320

                       |P   |80000000-00d2-b38f-4cda-000a0a412c00|E   |0
     |ADMIN_RECORDS

4d3680a5-0127-df6f-bee8-000000547e10|94bc40a2-0127-df6f-bee8-000000547e10|SQ11755321

                       |F   |80000000-00d2-b38f-4cda-000a0a412c00|E   |0
     |ADMIN_RECORDS

NULL                                |NULL
 |NULL
                                   |NULL|NULL
 |NULL|NULL       |PARAM1

Hence NULLs all over the places before and after some main records. So
R-JOINs works as expected.

I tried to correctly collect column-per-table pairs;

select SYS.SYSCOLUMNS.COLUMNNAME, SYS.SYSTABLES.TABLENAME from
SYS.SYSCOLUMNS, SYS.SYSTABLES, SYS.SYSCONSTRAINTS where
SYS.SYSTABLES.TABLEID = SYS.SYSCOLUMNS.REFERENCEID and
SYS.SYSTABLES.TABLENAME like 'SERVER%' and not
SYS.SYSTABLES.TABLEID=SYS.SYSCONSTRAINTS.TABLEID;
COLUMNNAME
                                                   |TABLENAME


-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ADMIN_RECORDS
                                                    |SERVER_SHUTDOWN_LOG


RECORD_ID
                                                    |SERVER_SHUTDOWN_LOG


SHUTDOWN_TYPE
                                                    |SERVER_SHUTDOWN_LOG


ID
                                                   |SERVER_SHUTDOWN_TYPE


NAME
                                                   |SERVER_SHUTDOWN_TYPE


ID
                                                   |SERVER_SHUTDOWN_TYPE


NAME
                                                   |SERVER_SHUTDOWN_TYPE



That worked fine. No duplicates...

That two tables appear in SYSCONSTRAINTS:

select SYS.SYSCONSTRAINTS.TABLEID from SYS.SYSCONSTRAINTS;
TABLEID
------------------------------------
2ba7c09b-0127-df6f-bee8-000000547e10
94bc40a2-0127-df6f-bee8-000000547e10
94bc40a2-0127-df6f-bee8-000000547e10

So:

SELECT CONSTRAINTNAME, COLUMNNAME FROM SYS.SYSTABLES t, SYS.SYSCOLUMNS col,
SYS.SYSCONSTRAINTS cons, SYS.SYSCHECKS checks WHERE t.TABLENAME like
'SERVER%' AND t.TABLEID = col.REFERENCEID AND t.TABLEID = cons.TABLEID AND
cons.CONSTRAINTID = checks.CONSTRAINTID ORDER BY CONSTRAINTNAME;
CONSTRAINTNAME
                                                   |COLUMNNAME


-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Выбрано строк: 0
ij> SELECT CONSTRAINTNAME, COLUMNNAME FROM SYS.SYSTABLES t, SYS.SYSCOLUMNS
col, SYS.SYSCONSTRAINTS cons, SYS.SYSCHECKS checks where t.TABLEID =
col.REFERENCEID AND t.TABLEID = cons.TABLEID AND cons.CONSTRAINTID =
checks.CONSTRAINTID ORDER BY CONSTRAINTNAME;
CONSTRAINTNAME
                                                   |COLUMNNAME


-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Выбрано строк: 0

But again - I do not see rows because constraints not appear to be mapped to
columns. John

Mime
View raw message