Solved

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

Posted on 2009-07-07
3
146 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

752 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