• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

How do I identify only the matched column value in SQL?

Table1:
Number      Type                  
1                     1      
1                     1      
1                     2      
1                     2      
5                     3            
6                     4      
7                     5      
7                     5      
7                     5      

Table2:
Type             Code1   Code2   Code3   Code4            
1                       A           B          NULL    NULL      
2                       F          NULL    NULL        C  
3                       D            B         NULL     NULL
4                       D          NULL        F        NULL      
5                       A            E              X            B                  

SELECT * FROM Table1 t1, Table2 t2
WHERE t1.Type = t2.Type
AND  (t2.Code1 IN (A, B)
   OR t2.Code2 IN (A, B)
   OR t2.Code3 IN (A, B)
   OR t2.Code4 IN (A, B))

Result Set:
Number  Type     Code1   Code2   Code3   Code4  
1               1             A            B            NULL       NULL
5               3             D            B            NULL       NULL
7               5             A            E               X            B  

How do I display a result set which will only show the code that matched?

This is what I want to return...
Number    Type    Code
1                 1            A      
5                 3            B      
7                 5            A
0
seckel
Asked:
seckel
  • 2
1 Solution
 
pssandhuCommented:
How about:
Select Number, Type, CASE WHEN CODE1 IN (A,B) Thne CODE1
                          WHEN CODE2 IN (A,B) Thne CODE2
                          WHEN CODE3 IN (A,B) Thne CODE3
                          WHEN CODE4 IN (A,B) Thne CODE4
                          ELSE 'N'
                     END as Code
FRom (
SELECT Number, t1.Type,  Code1, Code2, Code3, Code4
FROM Table1 t1, Table2 t2 
WHERE t1.Type = t2.Type 
AND  (t2.Code1 IN (A, B) 
   OR t2.Code2 IN (A, B)
   OR t2.Code3 IN (A, B)
   OR t2.Code4 IN (A, B))
) a 

Open in new window

0
 
seckelAuthor Commented:
It worked!  Thank you very much.
0
 
pssandhuCommented:
No Problem.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now