Den 2012-10-23 13:16 skrev Frank såhär:
> On 23/10/12 11:21, Kjell Rilbe wrote:
>> Den 2012-10-23 10:32 skrev Frank såhär:
>>> select R1.RDB$RELATION_NAME
>>> from RDB$RELATION_FIELDS F1
>>> join RDB$RELATIONS R1 on F1.RDB$RELATION_NAME = R1.RDB$RELATION_NAME
>>> where F1.RDB$FIELD_NAME ='EMP_NO' and
>>> (not exists(select R2.RDB$RELATION_NAME
>>> from RDB$RELATION_FIELDS F2
>>> join RDB$RELATIONS R2 on F2.RDB$RELATION_NAME=R1.RDB$RELATION_NAME
>>> where F2.RDB$FIELD_NAME ='PHONE_EXT'));
>> Although I can't see why it would give the indicated error, the query
>> seems utterly pointless. R2 and F2 are not correlated and so the
>> subquery will return all F2 records paired with all R2 records. I think
>> it's a typo and that the subquery's join condition should refer to R2,
>> not R1.
> No, this query returns all tables from employee.fdb
> that contain a field EMP_NO, but not PHONE_EXT.
>
> This is of course just a simplified example to show the 'feature' we
> stumbled upon.
I know the issue is being looked at by Dmitry Y. Great!
But your query still seems strange... Just trying to be helpful, so I
hope I'm not being a pain... :-)
Your subselect does in fact join F2 and R2 without givin any condition
to correlate them, so in the subselect each F2 record will be matched
with each and every R2 record. Your join condition in there states that
you only want F2 records with the same RELATION_NAME as in F1 and R1, so
the subselect will only look for PHONE_EXT fields in the tables where
you found EMP_NO, which is what you want. OK, but then why is R2 even
present in the subselect? You can simplify your query like this:
select R1.RDB$RELATION_NAME
from RDB$RELATION_FIELDS F1
join RDB$RELATIONS R1
on F1.RDB$RELATION_NAME = R1.RDB$RELATION_NAME
where F1.RDB$FIELD_NAME ='EMP_NO'
and not exists(
select 1
from RDB$RELATION_FIELDS F2
where F2.RDB$FIELD_NAME ='PHONE_EXT'
and F2.RDB$RELATION_NAME=R1.RDB$RELATION_NAME
);
Regards,
Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kj...@datadia.se
Telefon: 08-761 06 55
Mobil: 0733-44 24 64
------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_sfd2d_oct
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
