Multi to multi query

There are students, curriculum two tables is to many relationship, intermediate table is the score table, such as student table data is 1 piece of three, 2 Li Si 3 Wang Wu,
Course table data is 1 Chinese, 2 math, 3 foreign language, intermediate table is 11190, representing 1 records corresponding to the Zhang San language 90 points, it is what the students lack of query what class? If the associated query words is what students what lesson, how check did not.?

Started by Frederica at November 20, 2016 - 7:00 PM

First through the whole link query all people of all classes and then from the inside to filter out the scores of the course

Posted by Jessie at November 29, 2016 - 7:27 PM

The general idea is that...

with t1 as
(
     select 1 c1,'Zhang San' c2 from dual union all
     select 2 c1,'Li Si' c2 from dual union all
     select 3 c1,'Wang Wu' c2 from dual 
),t2 as
(
     select 1 c3,'Chinese' c4 from dual union all
     select 2 c3,'Mathematics' c4 from dual union all
     select 3 c3,'English' c4 from dual 
),t3 as
(
     select 1 c5,1 c6,1 c7,80 c8 from dual union all
     select 2 c5,1 c6,2 c7,66 c8 from dual union all
     select 3 c5,2 c6,1 c7,78 c8 from dual union all
     select 4 c5,3 c6,3 c7,95 c8 from dual 
)

select a2,a4
from
(select c1 a1,c2 a2,c3 a3 ,c4 a4 from t1,t2) a
left join 
(select c1 b1 ,c3 b3,c8 b8
from t1,t2,t3
where c1 = c6 and c3 = c7) b
on a1 = b1 and a3 = b3
where b8 is null

     a2     a4
--------------------------
1 the king five language
2 Li four English
3 pieces of three English
4 the king five mathematics
5 Li four mathematics

Posted by Jessie at December 01, 2016 - 7:47 PM

What are the students lack what course
Student and course form a Cartesian product, and then do a left connecting score table. No is absent

Posted by Tommy at December 03, 2016 - 8:12 PM