I have two tables in plsql
tblStudent:-
StudentId Name .........
1 A
2 B
tblDept:-
DeptId DeptName StudentId
1 Dep Aero 1
2 IT 1
3 Dep Maths 1
4 Dep Chemistry 2
I want to get studentId, with all its departments which starts with 'Dep' separated by semi colon, If i pass where StudentId = 1 in SELECT result should look like
StudentId DeptName
1 Dep Aero;Dep Maths
any help please?
If you want an empty list of departments when a student has no entries then you can use an outer join between the two tables, e.g.:
select s.studentid,
listagg(d.deptname, ';') within group (order by d.deptname) as deptnames
from tblstudent s
left join tbldept d on d.studentid = s.studentid
and deptname like 'Dep%'
group by s.studentid;
Demo using CTEs for your sample data, including a third student ID with no matching departments:
-- CTEs for sample data
with tblstudent (studentid, name) as (
select 1, 'A' from dual
union all select 2, 'B' from dual
union all select 3, 'C' from dual
),
tbldept (deptid, deptname, studentid) as (
select 1, 'Dep Aero', 1 from dual
union all select 2, 'IT', 1 from dual
union all select 3, 'Dep Maths', 1 from dual
union all select 4, 'Dep Chemistry', 2 from dual
)
-- actual query
select s.studentid,
listagg(d.deptname, ';') within group (order by d.deptname) as deptnames
from tblstudent s
left join tbldept d on d.studentid = s.studentid
and deptname like 'Dep%'
group by s.studentid;
STUDENTID DEPTNAMES
---------- ------------------------------
1 Dep Aero;Dep Maths
2 Dep Chemistry
3
Your data model looks odd though; you should probably have a department table which only has the department IDs and names, and then another tables that links each student to all of their departments - something like (in CTE form again):
-- CTEs for sample data
with tblstudent (studentid, name) as (
select 1, 'A' from dual
union all select 2, 'B' from dual
union all select 3, 'C' from dual
),
tbldept (deptid, deptname) as (
select 1, 'Dep Aero' from dual
union all select 2, 'IT' from dual
union all select 3, 'Dep Maths' from dual
union all select 4, 'Dep Chemistry' from dual
),
tblstudentdept (studentid, deptid) as (
select 1, 1 from dual
union all select 1, 2 from dual
union all select 1, 3 from dual
union all select 2, 4 from dual
)
-- actual query
select s.studentid,
listagg(d.deptname, ';') within group (order by d.deptname) as deptnames
from tblstudent s
left join tblstudentdept sd on sd.studentid = s.studentid
left join tbldept d on d.deptid = sd.deptid
and deptname like 'Dep%'
group by s.studentid;
STUDENTID DEPTNAMES
---------- ------------------------------
1 Dep Aero;Dep Maths
2 Dep Chemistry
3
Either way, if you only want to see a single student's results when add that as a where
clause, right before the group by
:
...
where s.studentid = 1
group by s.studentid;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments