1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

  2. Anuncie Aqui ! Entre em contato fdantas@4each.com.br

[SQL] MySQL join two tables and add extra columns

Discussão em 'Outras Linguagens' iniciado por Stack, Agosto 31, 2021.

  1. Stack

    Stack Membro Participativo

    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).

    The tables are weirdly formatted not sure why. Refer this

    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.

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

    Continue reading...

Compartilhe esta Página