user2383818 user2383818 - 6 months ago 28
SQL Question

Firebird - select distinct with multiple tables

I trying to get a list of fields grouped by relations, using RDB$Fields and RDB$Relation_Fields. Follows SQL:

select distinct
RDB$relation_Fields.RDB$relation_name as "Relation Name" ,
RDB$relation_Fields.RDB$field_name as "Field Name " ,
(select RDB$Fields.RDB$Field_Type as "Field Type"
from RDB$Fields
where RDB$Fields.rdb$Field_Name =
RDB$relation_Fields.RDB$Field_Name)
from
RDB$fields ,
RDB$relation_Fields
where
RDB$relation_Fields.RDB$relation_name = 'Database_Table' and
substring ( RDB$relation_name from 1 for 4) <> 'IDE$' and
substring ( RDB$relation_name from 1 for 4) <> 'MON$' substring ( RDB$relation_name from 1 for 4) <> 'RDB$'
order by
RDB$relation_name,
RDB$relation_Fields.rdb$field_name


I should get

Relation Name Field Name Field Type
------------- ---------- ----------
Database_Table Field1 TypeX
Database_Table Field2 TypeY
... ... ...


Instead, I'm gettting

Relation Name Field Name Field Type
------------- ---------- ----------
Database_Table Field1 <null>
Database_Table Field2 <null>
... ... <null>


Please show me the correct SQL. Thanks.

vkp vkp
Answer

First, the tables need to be joined. You would get a cross joined result otherwise.

Also, you don't need a correlated subquery for the fieldtype column, because the tables are already being joined (assuming you add a join condition--i added it based on Thorsten's comment).

select rf.RDB$relation_name as "Relation Name" ,
       rf.RDB$field_name    as "Field Name   " ,
       f.RDB$Field_Type     as "Field Type"
from RDB$fields f
JOIN RDB$relation_Fields rf on f.RDB$FIELD_NAME = rf.RDB$FIELD_SOURCE
where rf.RDB$relation_name = 'Database_Table'                
and substring (rf.RDB$relation_name from 1 for 4) not in ('IDE$','MON$','RDB$')
order by rf.RDB$relation_name, rf.RDB$field_name
Comments