Solved

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

Posted on 2009-07-07
3
144 Views
Last Modified: 2012-05-07
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
Comment
Question by:seckel
  • 2
3 Comments
 
LVL 17

Accepted Solution

by:
pssandhu earned 500 total points
ID: 24795915
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
 

Author Closing Comment

by:seckel
ID: 31600679
It worked!  Thank you very much.
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24796220
No Problem.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question