DatTrish DatTrish - 4 years ago 263
SQL Question

Combining results in second table when Joining two tables

I have these two tables that I can't change, and I am trying to query and combine some data together from both of them. What I would ideally like to do is to combine omit rows where FieldTime2 is 0, when there are more than two results for the same FieldID. If FieldTime2 is 0 and there is only one result, then I want the row displayed.

Could you possibly count the FieldID in Table2 and assign a number if count is greater than 1, or something like that?

Table 1

ID FieldID FieldTime
------------------------
1 A 10
2 B 15
3 C 20


Table 2

ID FieldID FieldTime2
------------------------
1 A 0
2 A 15
3 B 0
4 C 10


Desired Output:

FieldID FieldTime FieldTime2
------------------------------
A 10 15
B 15 0
C 20 10


My Output:

FieldID FieldTime FieldTime2
------------------------------
A 10 0
A 10 15
B 15 0
C 20 10


CODE:

SELECT
m.FieldID,
m.FieldTime,
n.FieldTime2,

from Table1 m
Join Table2 n
ON m.FieldID = n.FieldID

Where n.FieldTime > 0

cha cha
Answer Source

The simplest solution that comes to mind is to use a LEFT JOIN query, like this:

SELECT
    m.FieldID, 
    m.FieldTime,
    COALESCE(n.FieldTime2, 0) as FieldTime2,

from Table1 m
  left Join Table2 n
       ON m.FieldID = n.FieldID and n.FieldTime2 > 0

Where n.FieldTime > 0

if there are multiple non-zero records in the Table2 and only one is desired then the query will be a bit complex:

with n as (
select *,
ROW_NUMBER() OVER(PARTITION BY FieldID ORDER BY FieldTime2 DESC) rn
from Table2
)
SELECT
    m.FieldID, 
    m.FieldTime,
    COALESCE(n.FieldTime2, 0) as FieldTime2,

from Table1 m
  LEFT Join n
       ON m.FieldID = n.FieldID and n.rn = 1

Where n.FieldTime > 0
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download