DatTrish - 4 years ago 263

SQL Question

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**