0

I'm trying to join table 1 to table 2 to get table 3. (See desired output) However, I can't seem to get it to work since there are so many options since the table only contains one value. A left join doesn't seem to work.

I found this: Left Join without duplicate rows from left table

which seems to match my use case, but Outer Apply is not in PrestoDB.

I essentially want to match each row in T1 with a single one in T2.

Desired Output

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
apple1234
  • 23
  • 2

2 Answers2

1

If I understand correctly, you can use row_number():

select t1.*, t2.col3
from t1 left join
     (select t2.*, row_number() over (partition by col2 order by col3 nulls last) as seqnum
      from t2
     ) t2
     on t2.col2 = t1.col2 and t2.seqnum = 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Sorry, I made the sample T1,T2 as an example. In reality, I have multiple values of b all with values of c or null. Same with p - that example only filters for the one c value. – apple1234 Nov 12 '18 at 20:28
  • 1
    Gordon's logic fits your stated desired results. We can only give you what you ask for, and cannot pre-empt or imagine the rest of your data and requirements. Perhaps you'd like to expand your question to include some example data? (Or pre-create a SQLFiddle/DBFiddle link for us) – Caius Jard Nov 12 '18 at 20:33
  • 1
    @apple1234 . . . This answers the question that you have asked. If you have *another* question, ask it as a *new* question, with appropriate details, sample data, and desired results. – Gordon Linoff Nov 12 '18 at 20:35
1

If you don't have proper keys you get an m:n-join instead of 1:n. You can calculate a row number for both tables which acts (in combination with col2) as key for the following join:

select t1.col1, t1.col2, t2.col3
from 
 (
   select t1.*, 
      row_number() over (partition by col2 order by col2) as rn
   from t1
 ) as t
left join
 (
   select t2.*, 
      row_number() over (partition by col2 order by col2) as rn
   from t2
 ) as t2
 on t1.col2 = t2.col2 
and t1.rn = t2.rn;
dnoeth
  • 59,503
  • 4
  • 39
  • 56