CODEWITHSUNDEEP
sqloracle-database
Db_Learner
Db_Learner

Reputation: 5

Select Sub query - based on value

Need help in creating subquery.

I have two tables "Department Access Info" & "Subject Code Master Table" . Need to combine these tables in case if any Dept_code has access value as "ALL". Below is the sample information.

Department Access Info Table Input with Subject_Code as "ALL"

Dept_Code Subject_Code
101 aa1
102 ALL
103 cc1

In case there is Subject code with "ALL", then we need to refer Subject code specific table to get all subjects. For example for Dept_Code "102" has access to "ALL" subjects.

Input Type -2 with Dept_Code as 'ALL' and Subject_Code as 'ALL'

Dept_Code Subject_Code
101 aa1
102 ALL
103 cc1
ALL ALL

There is Dept_Code_Master Table with all Dep_Codes |Dept_Code| |---------| |101 | |102 | |103 | |104 |

Subject Code Master Table

Dept_Code Subject_Code
102 bb1
102 bb2
102 bb3
104 dd1

By Combining 101 , 102 & 103 department code final required result as follows:

Final Required Output: for Type-1 Input

Dept_Code Subject_Code
101 aa1
102 bb1
102 bb2
102 bb3
103 cc1

Final Required Output : Type 2 Input (ALL COMPANY, ALL ACCOUNT)

Dept_Code Subject_Code
101 aa1
102 bb1
102 bb2
102 bb3
103 cc1
104 dd1

**** Edited with Input2

Upvotes: 0

Views: 225

Answers (2)

Littlefoot
Littlefoot

Reputation: 142705

That's just outer join, is it not?

SQL> select a.dept_code,
  2    case when a.subject_code = 'ALL' then b.subject_code
  3         else a.subject_code
  4    end subject_code
  5  from code_master a left join subject_code_master b on a.dept_code = b.dept_code
  6  order by 1, 2;

DEPT_CODE  SUBJECT_CODE
---------- ---------------
101        aa1
102        bb1
102        bb2
102        bb3
103        cc1

SQL>

Upvotes: 1

Error_2646
Error_2646

Reputation: 3781

With the example you've provided it doesn't look like a subquery is necessary.

WITH CODE_MASTER AS
  ( SELECT '101' AS Dept_code,
           'aa1' AS Subject_Code
      FROM dual
      UNION
     SELECT '102' AS Dept_code,
           'All' AS Subject_Code
      FROM dual
      UNION
     SELECT '103' AS Dept_code,
           'cc2' AS Subject_Code
      FROM dual
  ),
SUBJECT_CODE_MASTER AS
  ( SELECT '102' AS Dept_code,
           'bb1' AS Subject_Code
      FROM dual
      UNION
     SELECT '102' AS Dept_code,
           'bb2' AS Subject_Code
      FROM dual
      UNION
     SELECT '102' AS Dept_code,
           'bb3' AS Subject_Code
      FROM dual
  )
SELECT CODE_MASTER.DEPT_CODE,
       COALESCE(SUBJECT_CODE_MASTER.subject_code, CODE_MASTER.subject_code) AS Subject_Code
  FROM CODE_MASTER 
  LEFT
  JOIN SUBJECT_CODE_MASTER
    ON CODE_MASTER.dept_code = SUBJECT_CODE_MASTER.dept_code
   AND CODE_MASTER.Subject_Code = 'All'

Upvotes: 0

Related Questions