Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In the given schema, there is a table A having column A1,A2,A3,A4

There are three more tables i.e. B,C,D

If the value in A4 is b, i have to lookup table B. If the value in A4 is c, i have to lookup table C. If the value in A4 is d, i have to lookup table D.

Currently, we have implemented using switch case i.e.

select * from A
where
CASE
  WHEN SUBSTRING(A4,4,3) IN ( '023','009','011','013','015','017','019','021')
    THEN A3 NOT IN ( select Z from B) 
  WHEN SUBSTRING(A4,4,3) IN ( '006','024','028','031')
    THEN A3 NOT IN ( select Z from C) 
  WHEN SUBSTRING(A4,4,3) IN ( '004','025')
    THEN A3 NOT IN ( select Z from D) 
END

Is there any better and more efficient way to resolve this problem?

share|improve this question
    
Yes dont use this SQL anti pattern but use an normalised table design. this query will trigger an full table scan on table A and fore every row in A 4 full more full table scans on tables B, C and D... dont try to solve one hard question in 1 query... –  Raymond Nijland Sep 25 '13 at 18:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.