Firebird 3.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select rdb$get_context('SYSTEM', 'ENGINE_VERSION') as version
     , rdb$character_set_name
from rdb$database;


VERSION
RDB$CHARACTER_SET_NAME


3.0.5
UTF8




create table Ticket_Headers (
  ticket_id integer primary key, 
  amount integer not null
)


create table Ticket_Body (
  ticket_id integer 
     REFERENCES Ticket_Headers(ticket_id) 
        ON DELETE CASCADE
        ON UPDATE CASCADE, 
  Seller_Name varchar(20) not null,
  Seller_Type varchar(20) not null,
  CONSTRAINT TicketBody_PK PRIMARY KEY (ticket_id, Seller_Name)
)  


create index idx_TicketBody_Type on Ticket_Body(Seller_Type)


insert into Ticket_Headers
select 1, 600 from rdb$database union all
select 2, 900 from rdb$database union all
select 3, 400 from rdb$database 
3 rows affected

insert into Ticket_body
select 1, 'Karen', 'Manager' from rdb$database union all
select 1, 'James', 'Trainee' from rdb$database union all
select 2, 'John',  'Manager' from rdb$database union all
select 3, 'James', 'Trainee' from rdb$database 
4 rows affected

select * from Ticket_Headers


TICKET_ID
AMOUNT


1
600

2
900

3
400




select * from Ticket_Body 


TICKET_ID
SELLER_NAME
SELLER_TYPE


1
Karen
Manager

1
James
Trainee

2
John
Manager

3
James
Trainee




select * from Ticket_Headers TH, Ticket_Body TB where TH.ticket_id = TB.ticket_ID


TICKET_ID
AMOUNT
TICKET_ID
SELLER_NAME
SELLER_TYPE


1
600
1
Karen
Manager

1
600
1
James
Trainee

2
900
2
John
Manager

3
400
3
James
Trainee




alter table Ticket_Headers
  add Seller_Top computed by 
  ( -- this parenthesis is required by COMPUTED BY SQL syntax
   ( -- this parenthesis is required to coerce SELECT from query to expression 
    select First(1) TB.Seller_Name
      from Ticket_Body TB
     where TB.ticket_id = Ticket_Headers.ticket_id
    order by TB.Seller_type /* Descending - if other order to be needed */ 
   ) 
  )
      


select * from Ticket_Headers


TICKET_ID
AMOUNT
SELLER_TOP


1
600
Karen

2
900
John

3
400
James
TICKET_ID	AMOUNT	TICKET_ID	SELLER_NAME	SELLER_TYPE
1	600	1	Karen	Manager
1	600	1	James	Trainee
2	900	2	John	Manager
3	400	3	James	Trainee