How to get a separate user account from two related but different tables

Question

I apologize for this, but SQL is not a strong point for me and seems to be like a lot of other queries, I can not transfer them to this situation successfully.

I have two tables that will be related by a common value (id and Issue) if there is a row in table 2.

I need to get a certain number of users who raise certain problems. I have users in both tables, and the user of table 2 takes precedence if it exists.

Table 1 always has REPORTER, but table 2 may not have a Stringvalue Name (fieldtype = 1). If there is a Stringvalue, then this is a "User", and Reporter can be ignored.

Table 1

| id | Reporter| Type |
| 1  | 111111  | 1    |
| 2  | 111111  | 2    |
| 3  | 222222  | 2    |
| 4  | 333333  | 1    |
| 5  | 111111  | 1    |
| 6  | 666666  | 1    |

table 2

|issue | Stringvalue | fieldType|
| 1    |  Fred       |    1     |
| 1    | bananas     |    2     |
| 2    |  Jack       |    1     |
| 5    |  Steve      |    1     |

4 (1,4,5,6), (111111,333333,666666) Stringvalues ​​(Fred, Steve). Distinct Users = 4 (Fred, 333333, Steve, 666666)

| id| T1.Reporter | T2.Name |
|  1|  Null       | Fred    |
|  4| 333333      | Null    |
|  5|  Null       | Steve   |
|  6| 666666      | Null    |

SQL!

:

 SELECT 
  table1.REPORTER,
  TO_CHAR(NULL) "NAME"
  FROM table1
  Where table1.TYPE =1
  AND table1.REPORTER <> '111111'
 Union 
SELECT 
  TO_CHAR(NULL) "REPORTER",
  table2.STRINGVALUE "NAME"
FROM table2,
  table1 
WHERE table2.ISSUE     = table1.ID
AND table2.fieldtype= 1
and table1.issuetype = 1

1 Reporter , .

exists , . , ID Issue, . .

, , , , , , , .

Answer 1

LEFT JOIN, fieldtype = 1:

SELECT 
    table1.id,
    CASE
        WHEN table2.Stringvalue IS NOT NULL THEN table2.Stringvalue
        ELSE table1.Reporter
    END AS TheUser
FROM table1
LEFT JOIN table2 ON table1.id = table2.issue AND table2.fieldType = 1
WHERE table1.Type = 1

:

+------+---------+
| id   | TheUser |
+------+---------+
|    1 | Fred    |
|    4 | 333333  |
|    5 | Steve   |
|    6 | 666666  |
+------+---------+

Answer 2

, left join count(distinct). , :

select count(distinct coalesce(stringval, reporter) )
from table1 t1 left join
     table2 t2
     on t1.id = t2.issue and t2.fieldtype = 1
where t1.id in (1, 4, 5, 6);

JOIN. : FROM. JOIN. -, , .