Bring more than one row in a SubQuery Sql Server

Question

Bring more than one row in a SubQuery Sql Server

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

0

I'm working with 2 queries and would like a way to put the 2 in one, but I'm having trouble.

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 mount a query in which I have the values of T1 complemented by the values of T2 using the comparative field of Date and PF,

In 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, I also could not implement a UNION for that.

I'm using SQL Server 2014

sql sql-server

asked by anonymous 15.03.2018 / 20:58

3 answers

Keyboard squeezing components Why does "git checkout." not change the packages of node_module that I entered by npm?

score 1 · 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 links tables is PF , so just a inner join to bring the result you need.

If Data is also part of the condition, best to use where , like this:

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

score 0 · Answer 2

0

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

15.03.2018 / 21:15

score 0 · Answer 3

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

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

This is a solution that uses LEFT OUTER JOIN and uses the two columns mentioned above to establish the connection 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;