How to merge one row with two sql rows

Question

This might be a very simple, but I couldnt make it to work.Hope I get some help.

I have a select statement which returns the follow data

Id  Name  Phone  msg     File
1    A    null   null     f1

another select statement

Id     Name   Phone   msg      File
NULL   NULL    123   nothing     f1
NULL   NULL    156   nothing1    f1

How do I merge the above as

Id     Name   Phone   msg      File
1       A      123   nothing     f1
1       A      156   nothing1    f1

I tried using max and group by on File. Any help is appreciated.

Thanks in advance

Answer 1

Select s1.ID,s1.Name,s2.Phone,s2.msg,s2.File
from s1
Join s2 on s1.file=s2.file

Answer 2

Query:

SQLFIDDLEExample

SELECT t1.[Id],
       t1.[Name],
       t2.[Phone],
       t2.[msg],
       t2.[File]
FROM Table1 t1
JOIN Table2 t2 ON t1.[File]=t2.[File]

Result:

| ID | NAME | PHONE |      MSG | FILE |
---------------------------------------
|  1 |    A |   123 |  nothing |   f1 |
|  1 |    A |   156 | nothing1 |   f1 |