-1

I am looking for a method that extracts the dates of Result 2, add to be columns of Result 1, final as Expect result.

After Googled, I tried to use transform but mess result. How could I build the result? Thanks.

Result 1:

merchantRef totalAmount country
1 10 UK
2 20 UK
3 60 UK 
SELECT tblThree.merchantRef, tblThree.inDate
FROM tblThree;

Result 2 (tblThree):

merchantRef inDate
1 12/21/2010
2 02/28/2021
3 06/15/2021
3 07/15/2021

Expect result:

merchantRef totalAmount country inDate inDate2
1 10 UK 12/21/2010
2 20 UK 02/28/2021
3 60 UK 06/15/2021 07/15/2021
philipxy
  • 14,867
  • 6
  • 39
  • 83
Norman CF
  • 13
  • 4
  • Why are you only taking indates for merchant 3? – Gordon Linoff Mar 18 '21 at 02:06
  • thanks, the inDates is coming from another table. Like, for example, transaction dates of a merchanRef – Norman CF Mar 18 '21 at 02:10
  • Does this answer your question? [LEFT JOIN vs. LEFT OUTER JOIN in SQL Server](https://stackoverflow.com/questions/406294/left-join-vs-left-outer-join-in-sql-server) –  Mar 18 '21 at 02:10
  • thanks, tried but SQL join cannot transpose inDate in Result 2 to columns in Expect result... – Norman CF Mar 18 '21 at 02:14

1 Answers1

0

You can use a lateral join -- which is like a subquery that can return multiple rows:

select t1.*, t3.*
from table1 t1 outer apply
     (select nullif(min(t3.indate), max(t3.indate)) as indate1,
             nullif(max(t3.indate), min(t3.indate)) as indate2
      from table3 t3
      where t3.merchantRef = t1.merchantRef
     ) t3;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks Gordon, will try later. And may I have your comment that what if more than two inDates with same merchanRef ? thanks – Norman CF Mar 18 '21 at 02:23
  • @NormanCF . . . This question is specifically about returning two columns of values. If you have a different question, ask it as a *new* question. – Gordon Linoff Mar 18 '21 at 02:43
  • @ Gordon... thanks for remind. Sorry about that I made an unclear question. I think I will edit again this question since infact, the result 2 have not only 2 similar merchantRef. – Norman CF Mar 18 '21 at 07:58