-1

I have three tables as shown below. The topics_table is to be created by inserting values from grades_table and subjects_table and a few other fields that are either taken in as input(topic_name) or calculated(revision dates).

grades_table

grade_id grades sections
1 1 A
2 1 B

subjects_table

subject_id grade_id teacher_id subject_name
1 1 1 Maths
2 2 2 English

topics_table (NOTE: this table is currently empty).

I want to add values of grade_id and subject_id from the grades and subjects table to this table. Basically, I am thinking of joining the subject table and grades table and extracting the required values from that and put it in this table under grade id and subject id columns while also putting in topic_name from the user and revision dates from the system.

topic_id grade_id subject_id topic_name revision_one revision_two revision_three
1 1 1 Adding Fractions 28-10-2021 28-11-2021 28-12-2021
2 2 2 Nouns 01-11-2021 01-12-2021 01-01-2022

Right now, I am using join to insert the values of grade_id and subject_id in the topics_table, but the issue is that I am not able to receive the topic_name and revision dates as they are extra columns that cannot be taken from another table using joins. Below is the MySQL query that I'm using to obtain topics_table right now. It only gives grade_id and subject_id.

How do I also get the extra columns?

Note that this table will be updated each time using the same query when the input for topic_name is entered.

Here, $chooseGrade, $chooseSubject, $chooseSection are the inputs that I will be receiving from the user via a form. $teacherId is received from another table using a different query.

Let me know if there is another way of taking in values from grades table and subjects table and putting it in the topics table. I tried to use SQL variables but even that didn't work. The code for that is also given.

INSERT INTO topics_table (subject_id, grade_id, $topicName , CURRENT_DATE(),CURRENT_DATE()+INTERVAL 1 DAY,CURRENT_DATE()+INTERVAL 7 DAY,CURRENT_DATE()+INTERVAL 30 DAY,CURRENT_DATE()+INTERVAL 90 DAY,CURRENT_DATE()+ INTERVAL 180 DAY)
SELECT G.grade_id, S.subject_id
FROM grades_table G
INNER JOIN subjects_table S
WHERE G.grade_id = S.subject_id
AND G.grades  = $chooseGrade
AND G.sections = $chooseSection
AND S.subject_name = $chooseSubject
AND S.teacher_id = $teacherId 

//using variables NOT joins

SELECT  grade_id INTO @grade_id FROM grades_table WHERE grades = '$chooseGrade' AND sections = '$chooseSection';
SELECT subject_id INTO @subject_id FROM subjects_table WHERE grade_id = @grade_id AND subject_name = '$chooseSubject' AND teacher_id = '$teacherId';
INSERT INTO topics_table ( subject_id, grade_id, topic_name, revision_one, revision_two, revision_three) VALUES (@subject_id, @grade_id,'$topicName',CURRENT_DATE()+INTERVAL 30 DAY,CURRENT_DATE()+INTERVAL 60 DAY,CURRENT_DATE()+ INTERVAL 90 DAY);
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Bhavika Choudhary
  • 37
  • 1
  • 4

2 Answers2

0

you will have noticed that i replaced the variable with placeholders

See pleasr this topic about sql injection

Another thing is you joining form your query doesn't seem that correct to me, because Grades and subject should be referenced by grades_id and not be subject_id

the same goes for topics_table grades_id, seems also redundant, so you should take a look at normalisation

SELECT G.grade_id, S.subject_id,t.topic_name,t.revision_one,    t.revision_two ,    t.revision_three
FROM grades_table G
INNER JOIN subjects_table S ON G.grade_id = S.grade_id 
INNER JOIN topics_table t ON t.grade_id = s.grade_id AND t.subject_id = s.subject_id

WHERE G.grades  = ?
AND G.sections = ?
AND S.subject_name = ?
AND S.teacher_id = ?
nbk
  • 45,398
  • 8
  • 30
  • 47
  • oh actually topic_name and subject_name are different. I just edited the question – Bhavika Choudhary Aug 30 '21 at 22:55
  • But how can I reference subject_name by grades_id when subject_name is in the subjects table? And fixed the grades_id in topics_table. I see that you were actually focussing on the values inside the table. I actually put them randomly and did not pay attention since I thought that won't matter. I tried to use the code that you've written but it still won't work for me. Also, it still doesn't fix the issue. How will I put in the values of topic_name received from input and revision dates taken as current date? – Bhavika Choudhary Aug 30 '21 at 23:33
  • i viewed you table an see lnks between them which are in the on clause , a [mre] must represent you actual data, else we come to wrong conclusions, so to get a better idea ad a concrete query with the ids embedded and wanted result from that query, but still the query as it is should be useful – nbk Aug 30 '21 at 23:50
0

You should use merge if you want to get a single table as output

MERGE Subject S
USING Grade G
ON (S.SubjectID = G.GradeID)
WHEN MATCHED
THEN UPDATE SET
COLUMNAME = COLUMNNAME,
COLUMNNAME = COLUMNANAME
WHEN NOT MATCHED BY TARGET
THEN INSERT (XYZ, ABC, ABZ)
VALUES(XYZ,ABC,ABZ)
WHEN NOT MATCHED BY SOURCE
THEN DELETE

note: use your own values in place of xyz,abc etc

Muhammad Mujtaba Shafique
  • 299
  • 2
  • 12