CODEWITHSUNDEEP
sqloracle-databaseunion
Martin
Martin

Reputation: 43

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

Apologies for this but SQL is not a strong point for me, and whilst appears similar to lots of other queries I cannot translate those to this situation successfully.

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

I need to get a distinct count of users raising particular issues. I have users in both tables, with the table 2 user taking precedence if it exists.

There is always a REPORTER in Table 1, but there may not be a Stringvalue of Name (fieldtype = 1) in table 2. If there is a Stringvalue then that is the "User" and the 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     |

I have a total of 4 issues of the right type (1,4,5,6), three reporters (111111,333333,666666) and two Stringvalues(Fred, Steve). My 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 do I get this result in SQL!

Closest try so far:

 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

Without explicitly excluding the default table 1 Reporter, this gets returned in my results even when there is a name value in table 2.

I have tried exists and in but cannot get syntax right or the correct results. As soon as try any Join that links the ID and Issue values the results always end up constrained to the matching rows or for all values. And added additional conditions to the ON does not return correct results.

I have tried too many permutations to list, logically this sounds like should be able to do union with where exists, or left outer join but my skills are lacking to make this work.

Upvotes: 4

Views: 84

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272306

You need to use a LEFT JOIN and that is where you specify the fieldtype = 1 clause:

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

Result:

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If I understand correctly, you want a left join and count(distinct). Here is what I think you are looking for:

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);

You need to learn how to use explicit JOIN syntax. As a simple rule: Never use commas in the FROM clause. Always use explicit JOIN syntax. For one thing, it is more powerful, making it easy to express outer joins.

Upvotes: 1

Related Questions