Delete a row from a single table by doing INNER JOIN from several tables

Question

Delete a row from a single table by doing INNER JOIN from several tables

Navigation

#1 by (1 votes)

1

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    |                |
+-------------+-----------------+------+-----+---------+----------------+

subjects :

+---------------+-----------------+------+-----+---------+----------------+
| 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'm trying to do is delete a subject from the subjects table knowing the user id and the name of the subject that you want to delete, what I have So far it 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, it returns the following error:

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

Thanks in advance for any help ....

sql php pdo

asked by lromeraj 27.06.2017 в 21:53

source

1 answer

Save Dataset to table sql server in window form c # Keep the countdown in Forms

score 1 · Accepted Answer

The sentence would be as follows:

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 your answer will help, greetings.