I have 2 tables similar to this
Table 1 – user table.
| U_ID | STATUS | TR_CODE |
|---|---|---|
| 1 | open | NA |
| 2 | closed | SUC |
| 3 | closed | CAN |
| 4 | closed | INV |
| 5 | closed | SUC |
| 6 | closed | DEN |
| 5 | closed | NA |
| 6 | closed | DEN |
Table 2 – lookup table.
| LK_CD | LK_DSC |
|---|---|
| SUC | Success |
| CAN | Cancelled |
| INV | Invalid |
| DEN | Denied |
Now I’m trying to get the count of different TR_CODE where STATUS is closed. I need to get the relevant description of TR_CODE from table 2 and group it accordingly
I used below query
SELECT LK_DSC,count(*) as num from user u,lookup where
LK_CD = u.TR_CODE and u.STATUS = 'closed' GROUP BY LK_DSC
This is giving me the required data for all TR_CODE that are present in lookup table.But if it is not in lookup table it is not showinng(eg: NA is not getting listed)
How to fetch that record as well if not in lookup table and show TR_CODE name itself for NA
Like Below
LK_DSC Count
Success 1
Cancelled 1
Invalid 1
Denied 2
NA 2
Any help would be appreciated