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