How to make sql query using two tables

Question

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

table T1:

Table T2:

You need to familiarize yourself with JOINS, there is plenty of information online that can be a great place to start. You need to understand these concepts instead of coming here looking for an answer. Better to teach a man to fish than to give him a fish, even better if he can learn to teach himself how to fish. — haag1
– haag1, Commented Apr 12, 2019 at 14:58

sabhari karthik · Accepted Answer · 2019-04-12 16:03:31Z

1

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";

edited Apr 12, 2019 at 16:03

answered Apr 12, 2019 at 15:08

sabhari karthik

1,3818 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Blue Over a year ago

SQL Error: ORA-00904: "class_student_map"."class": invalid identifier, plus in T3 A and B are keys of class/student

sabhari karthik Over a year ago

Your second point is correct. I have updated the query now. But, the error says, whatever the table (in this case T2) referred by class_student_map doesn't have an attribute class. Can you double check table names and attribute names once?

sabhari karthik Over a year ago

I still didn't find anything wrong with the query. So, I have just edited it now to have double quotes everywhere i used alias. Can you try it now..?

Ikacho · Accepted Answer · 2019-04-12 18:37:30Z

1

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;

edited Apr 12, 2019 at 18:37

answered Apr 12, 2019 at 16:18

Ikacho

751 gold badge1 silver badge6 bronze badges

1 Comment

d_kennetz Over a year ago

Hi! Please explain why this is a solution to OPs code as code only answers are discouraged on SO. This would help OP and future visitors to the site. Thanks!

Collectives™ on Stack Overflow

How to make sql query using two tables

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related