Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-07-07
3
Medium Priority
?
149 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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

886 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