Solved

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

Posted on 2009-07-07
3
143 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

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

777 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