Re: Tricky Join problem .. ?
In article <35a40fe5.224348636@news.imaginet.fr>,
jmullee@nirvanet.net (John Mullee) wrote:
>
> I wrote this as a solution to my previous posting
> (?? Getting info on foreign keys from sys*),
> but i'm not happy with it.
>
> select T2.tabname as Parent, M2.colname as ParentCols,
> T1.tabname as Child, M1.colname as ChildCols
> from sysreferences R,
> sysconstraints C1, sysconstraints C2,
> systables T1, systables T2,
> sysindexes I1, sysindexes I2,
> syscolumns M1, syscolumns M2
> where C1.constrtype = 'R'
> and C1.tabid = T1.tabid
> and C1.idxname = I1.idxname
> and R.constrid = C1.constrid
> and R.ptabid = T2.tabid
> and R.primary = C2.constrid
> and C2.idxname = I2.idxname
> and M2.tabid = I2.tabid
> and M2.colno = I2.part1
> and M1.tabid = I1.tabid
> and M1.colno = I1.part1
> order by Parent, Child;>
> it returns something like:
> Parent ParentCols Child ChildCols
> -----------------------------------------------
> webmimetypes object_type webpages object_type
> webprojects project webpages project
>
> .. which doesn't handle multiple-part indexes.
> Or User-defined types, or functional indexes.. Hmmm.
> any ideas?
>
> I tried using sysindices instead of sysindexes, but the indexkeys
> column resists attempts to parse it and feed it to syscolumns...
>
> Informix staff listening?!? ~8)
>
> TIA; John.
>
>
i am testing....
--
Daniel Santana
System Analyst
Informix 4gl 6.01 | SCO Open Server 3.0
-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum