Home / Questions / 81990
Accepted
lromeraj
Asked: 2020-06-28 11:53:43 +0800 CST 2020-06-28 11:53:43 +0800 CST

Delete a row from a single table by doing INNER JOIN of multiple tables

  • 772

I have the following tables:

users :

+----------------+-----------------+------+-----+-------------------+-----------------------------+
| Field          | Type            | Null | Key | Default           | Extra                       |
+----------------+-----------------+------+-----+-------------------+-----------------------------+
| id             | int(6) unsigned | NO   | PRI | NULL              | auto_increment              |
| firstname      | varchar(30)     | NO   |     | NULL              |                             |
| lastname       | varchar(30)     | NO   |     | NULL              |                             |
| birthday       | datetime        | NO   |     | NULL              |                             |
| email          | varchar(50)     | NO   |     | NULL              |                             |
| valid          | tinyint(1)      | NO   |     | 0                 |                             |
| validationCode | varchar(255)    | NO   |     | NULL              |                             |
| password       | varchar(255)    | NO   |     | NULL              |                             |
| reg_date       | timestamp       | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+----------------+-----------------+------+-----+-------------------+-----------------------------+

courses :

+-------------+-----------------+------+-----+---------+----------------+
| Field       | Type            | Null | Key | Default | Extra          |
+-------------+-----------------+------+-----+---------+----------------+
| course_id   | int(6) unsigned | NO   | PRI | NULL    | auto_increment |
| course_name | varchar(50)     | NO   |     | NULL    |                |
| user_id     | int(6) unsigned | NO   | MUL | NULL    |                |
+-------------+-----------------+------+-----+---------+----------------+

subject :

+---------------+-----------------+------+-----+---------+----------------+
| Field         | Type            | Null | Key | Default | Extra          |
+---------------+-----------------+------+-----+---------+----------------+
| subject_id    | int(6) unsigned | NO   | PRI | NULL    | auto_increment |
| subject_name  | varchar(255)    | NO   |     | NULL    |                |
| teacher_name  | varchar(255)    | NO   |     | NULL    |                |
| subject_room  | varchar(30)     | NO   |     | NULL    |                |
| subject_color | varchar(50)     | NO   |     | NULL    |                |
| course_id     | int(6) unsigned | NO   | MUL | NULL    |                |
+---------------+-----------------+------+-----+---------+----------------+

What I intend to do is delete a subject from the table subjectsknowing the user id and the name of the subject to be deleted, what I have so far is the following:

$drop = $conn->prepare("
        DELETE subjects.*
        FROM subjects users
        INNER JOIN courses ON users.id = courses.user_id
        INNER JOIN subjects ON courses.course_id = subjects.course_id
        WHERE users.id = :userid AND subjects.subject_name = :subjectName
    ");

Doing this gives me the following error:

Column not found: 1054 Unknown column 'users.id' in 'where clause''

Thanks in advance for any assistance....

php

1 Answers

  1. Best Answer

    The sentence would be the following:

    DELETE s    
FROM users as u
INNER JOIN courses as c 
    ON u.id = c.user_id
INNER JOIN subjects as s 
    ON c.course_id = s.course_id
WHERE u.id = :userid AND s.subject_name = :subjectName

    I hope my answer helps you, greetings.

    • 1