CODEWITHSUNDEEP
sqldatabaseoracle-database
Queryguy
Queryguy

Reputation: 37

oracle sql query - populating values based on priority

tab1 has details of marks published for students. now all the marks are not out yet , based on empid few are published.

tab1

subgrp,empid,sub,marks
1     ,4    ,phy,  90
1     ,4    ,ls ,  44
1     ,5    ,eng,  80
1     ,5    ,eco,  66

tab2 has details of all the subjects present in semester along with the priority.

tab2

subgrp,sub,priority
1     ,phy,    1
1     ,math,   2
1     ,ls  ,   3
1     ,eng ,   4
1     ,eco,    5

tab 1 has details of marks already published for subjects for each of the student. tab 2 has details of all the subjects present in the subgrp that is in the semister.

i would like to write a query to find out following ..

if you observe closely tab2 is my main table and tab1 is left joined so that i can consider all the values of tab2.

output of the query 

subgrp,empid,subject,marks,priority
1     ,4    ,phy    ,90   ,1
1     ,4    ,math   ,null ,2
1     ,4    ,ls     ,90   ,3
1     ,4    ,eng    ,null ,4
1     ,4    ,eco    ,null ,5
1     ,5    ,phy    ,null ,1
1     ,5    ,math   ,null ,2
1     ,5    ,ls     ,null ,3
1     ,5    ,eng    ,80   ,4
1     ,5    ,eco    ,66   ,5

query i tried to write, incomplete since i am unable to convert the logic into sql..

 select a.subgrp,a.emoid , a.sub as subject,a.marks,b.priority from
 tab2 a left join tab1 b on a.subgrp=b.subgrp and a.sub=b.sub

i am unable write down the logic, how to populate 5 records for per emp-id based on the tab2 data.

can anyone please help ..

Upvotes: 1

Views: 62

Answers (2)

Popeye
Popeye

Reputation: 35910

You can use cross join and then group by on it.

Select t1.subgrp, 
       t1.empid, 
       t2.subject,
       Max(t1.marks) as marks, 
       t2.priority
  From tab1 t1 cross join tab2 t2
  Group by t1.subgrp, 
           t1.empid, 
           t2.subject, 
           t2.priority

Cheers!!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269823

Use a cross join to generate the rows and a left join to bring in the data:

select t2.substr, e.empid, t2.subject, t.marks, t2.priority
from (select distinct empid from tab1
     ) e cross join
     tab2 t2 left join
     tab1 t
     on t.empid = e.empid and
        t.subgrp = t2.subgrp and
        t.sub = t2.sub
order by e.empid, t2.priority;

Upvotes: 3

Related Questions