0

tl:dr This command doesn't work :(

SELECT * FROM homework WHERE crn =138628 AND WHERE id NOT IN (SELECT id FROM studentHw WHERE codigoAlumno="214521671");

Sorry for spanish names

I want to compare two tables to find what id isn't in another table . I have two tables:

Homework Table: with a group code (crn) and an id(identify the homework)

+------------+------------+--------+-----+
| titulo     | fecha      | crn    | id  |
+------------+------------+--------+-----+
| Tarea 1    | 2018-03-14 | 138628 |  25 |
| Tarea 2    | 2018-03-14 | 138628 | 158 |
| Tarea 3    | 2018-03-14 | 138628 | 159 |
| Tarea 1    | 2018-03-15 | 125488 | 162 |
| Tarea 2    | 2018-03-15 | 125488 | 163 |
+------------+------------+--------+-----+



SELECT * FROM homework ;

I want to select only the elements with the crn equals to 138628 and after compare it with another table named studentHW (delivered homework):

studentHW

+-----+--------------+---------+--------+
| id  | codigoAlumno | entrega | crn    |
+-----+--------------+---------+--------+
| 25  | 214521671    | si      | 138628 |
| 158 | 214521671    | si      | 138628 |
| 159 | 214521671    | si      | 138628 |
+-----+--------------+---------+--------+

SELECT * FROM studentHw;

I want to select all rows in Homework that are not in studentHomework

I check in stackoverflow for the answer I found this...

Mysql: Select rows from a table that are not in another

And I try with the following command but it doesn't work:

SELECT * FROM homework WHERE crn =138628 AND WHERE id NOT IN (SELECT id FROM studentHw WHERE codigoAlumno="214521671");

codigoalumno is a student id

I receive this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id NOT IN (SELECT id FROM studentHw)' at line 1
Jorge
  • 3
  • 3

1 Answers1

1

The WHERE keyword should only be used once in a query.

Correct code:

SELECT * FROM homework WHERE crn =138628 AND id NOT IN (SELECT id FROM studentHw WHERE codigoAlumno="214521671");
Joas
  • 1,796
  • 1
  • 12
  • 25
  • Thank you, I did not know that I could only use Where once – Jorge Apr 16 '18 at 21:10
  • It's not a matter of CAN, it;s a matter of SHOULD. the WHERE keyword specifies that the WHERE clause is starting. After the WHERE keyword you can specify any number of conditions for the rows you want to select using the AND and OR keywords. I suggest following some basic tutorials on SQL before trying to write queries. w3schools explains things simply and can be a good place to start https://www.w3schools.com/sql/sql_intro.asp @Jorge – Joas Apr 16 '18 at 21:13