sql - how to get a Distinct Count of users from two related but different tables -

apologies sql not strong point me, , whilst appears similar lots of other queries cannot translate situation successfully.

i have 2 tables related common value (id , issue) if row in table 2 exists.

i need distinct count of users raising particular issues. have users in both tables, table 2 user taking precedence if exists.

there reporter in table 1, there may not stringvalue of name (fieldtype = 1) in table 2. if there stringvalue "user" , reporter can 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     | 

i have total of 4 issues of right type (1,4,5,6), 3 reporters (111111,333333,666666) , 2 stringvalues(fred, steve). total count of distinct users = 4 (fred, 333333, steve, 666666)

result table

| id| t1.reporter | t2.name | |  1|  null       | fred    | |  4| 333333      | null    | |  5|  null       | steve   | |  6| 666666      | null    | 

how result in sql!

closest try far:

 select    table1.reporter,   to_char(null) "name"   table1   table1.type =1   , table1.reporter <> '111111'  union  select    to_char(null) "reporter",   table2.stringvalue "name" table2,   table1  table2.issue     = table1.id , table2.fieldtype= 1 , table1.issuetype = 1 

without explicitly excluding default table 1 reporter, gets returned in results when there name value in table 2.

i have tried exists , in cannot syntax right or correct results. try join links id , issue values results end constrained matching rows or values. , added additional conditions on not return correct results.

i have tried many permutations list, logically sounds should able union exists, or left outer join skills lacking make work.

you need use left join , specify fieldtype = 1 clause:

select      table1.id,     case         when table2.stringvalue not null table2.stringvalue         else table1.reporter     end theuser table1 left join table2 on table1.id = table2.issue , table2.fieldtype = 1 table1.type = 1 

result:

+------+---------+ | id   | theuser | +------+---------+ |    1 | fred    | |    4 | 333333  | |    5 | steve   | |    6 | 666666  | +------+---------+