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
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
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