Bring more than one line into a Subquery Sql Server

Question

I am working with 2 consultations and would like a way to unite the 2 in one, however I am having difficulties.

I have the following example:

Table T1:

  CD    PF        Data           Total
  1     JSL001    15/03/2018     100 

Table T2:

  ID    PF      Data        Motivo  Total
  45    JSL001  15/03/2018  X       85 
  46    JSL001  15/03/2018  Y       15 

I need to assemble a query in which I have the values of T1 complemented by the values of T2 using as a comparative factor the field of Data and PF,

This way the expected result would be as follows:

  CD    PF      Data         Total  T2.Motivo   T2.Total
  1    JSL001   15/03/2018   100     X          85 
  1    JSL001   15/03/2018   100     Y          15 

I tried with Subqueryes, but when it returns more than one line it does not accept, also I could not implement a UNION for this.

I am using SQL Server 2014

Answer 1

Why not using join?

select t1.CD, t1.PF, t1.Data, t1.Total,
       t2.Motivo, t2.Total
  from t1 inner join t2 on t1.pf = t2.pf

From what you’ve shown, the field that connects the tables is PF, then just one inner join to bring the result you need.

If Data is also part of the condition, use the where, thus:

select t1.CD, t1.PF, t1.Data, t1.Total,
       t2.Motivo, t2.Total
  from t1, t2 
 where t1.pf = t2.pf 
   and t1.Data = t2.Data

Answer 2

Simply use INNER JOIN. Follow the example in SQL Fiddle HERE

SELECT T1.*, 
        T2.Motivo AS MotivoT2, 
        T2.Total AS TotalT2
FROM T1
INNER JOIN T2 ON T2.PF = T1.PF

Answer 3

I need to assemble a query in which I have the values of T1 complemented by the values of T2 using as a comparative factor the field of Data and PF

The solution, as already commented in other answers, is in the junction between T1 and T2 tables.

Here is the solution that uses LEFT OUTER JOIN and uses the two columns mentioned above to establish the link between the tables.

-- código #1
SELECT T1.CD, T1.PF, T1.Data, T1.Total,
       T2.Motivo as [T2.Motivo], T2.Total as [T2.Total]
  from T1
       left join T2 on T1.PF = T2.PF
                       and T1.Data = T2.Data;