CODEWITHSUNDEEP
sqljoinoracle-sqldeveloper
Blue
Blue

Reputation: 71

How to make sql query using two tables

I have 3 tables T1,T2 and T3. Table T1 contains 2 columns (key,class/student). The column class/student contains both classes and students, for example: 'english', 'math', 'mark', 'tom'... Table T2 contains 2 columns (class,student). Each class have more than one student in it, and these 2 columns use the keys from T1. In Table T3 I want to insert a specific class with its student(s) - class(es) into column A and student(s) into column B. Knowing that these columns use the keys from T1 table I've tried this but it returns same specific class with its students multiple times:

INSERT INTO T3 (A,B) 
    SELECT m.class, m.student 
    FROM T1 b,T2 m
    WHERE m.class = (SELECT key FROM T1 WHERE class/student='English') 
      AND b.KEY = m.student;

the result i get: 1 is id of class english ,10 is id of student mark, 11 is id of student tom

enter image description here

table T1:

enter image description here

Table T2:

enter image description here

Upvotes: 0

Views: 107

Answers (2)

Ikacho
Ikacho

Reputation: 75

Here you have simple code and just one join operation, which positively impacts on performance:

insert into t3 
WITH spec_class AS
(select key from T1 where class_student='English')
SELECT m.class, m.student 
FROM T2 m inner join spec_class sp on m.class=sp.key;

Upvotes: 1

sabhari karthik
sabhari karthik

Reputation: 1371

Since your master table T1 has both class and student details, I would recommend joining it twice with the class_student_map table T2 to get the details:

INSERT INTO T3 (A,B) 

SELECT "class_master"."key", "student_master"."key"
FROM 
T2 "class_student_map"
INNER JOIN
T1 "class_master"
ON
"class_master"."key" = "class_student_map"."class"
AND
"class_master"."class/student" = 'English'
INNER JOIN
T1 "student_master"
"student_master"."key" = "class_student_map"."student";

Upvotes: 1

Related Questions