0

I have 2 tables in teiid vdb table a and table b, for every row in table a the table b can contain one or multi rows, how to join these 2 tables such that for every record in table a there is one match from table b? if table b contains more than one row join should give any one single row.

currently i am writing it as but the performance is slow

select 
(select e from b where b.source_file_name = a.source_file_name limit 1) e, 
(select f from b where b.source_file_name = a.source_file_name limit 1) f,
(select g from b where b.source_file_name = a.source_file_name limit 1) g,
(select h from b where b.source_file_name = a.source_file_name limit 1) h,
a.*
from a where a.source_file_name = 'test'
Vaibav
  • 77
  • 1
  • 7
  • so table b has duplicated rows? otherwise, it is `select a.*,b.* from a left join b on a.source_file_name = b.source_file_name` may be you can use `distinct` on one of the columns on table a to limit to 1 – Ramesh Reddy Jun 16 '20 at 20:04
  • distinct cat work because table b can have different rows for each filename, i just need any one row from table b for each record in table a. – Vaibav Jun 19 '20 at 20:59

1 Answers1

0

Assuming that the whole query is not being pushed down from what you have shown if you are always providing the a.source_file_name predicate, then Teiid should be optimizing so that at worst there is 1 outer query and then 4 additional queries for the b table information. Are you seeing more than that and/or running the query without specifying a.source_file_name?

The simplest solution is to use a lateral join:

select a.*, b.* from a lateral (select e, f, g, h from b where b.source_file_name = a.source_file_name limit 1) b where a.source_file_name = 'test'

That effectively says for each row from a perform that join.

Steven Hawkins
  • 538
  • 1
  • 4
  • 7