CODEWITHSUNDEEP
sqlfirebirdfirebird2.5
user2383818
user2383818

Reputation: 689

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 getting

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

Please show me the correct SQL.

Upvotes: 0

Views: 71

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

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

Upvotes: 3

Related Questions