There is a database with several tables: groups (id, name) , students (id, name, surname, gr_id) , marks (id, st_id, d_id, t_id, mark) .

It is necessary to calculate the number of groups that are rated. Swears about the syntax near FROM marks) AS :

 SELECT COUNT(DISTINCT groups.id) FROM groups JOIN students JOIN (SELECT DISTINCT st_id FROM marks) AS der_table ON (groups.id = students.gr_id AND students.id = der_table.st_id);

Help, please, I want to understand this question.

Closed due to the fact that off-topic participants Denis , Kromster , cheops , Bald , aleksandr barakin 25 Oct '16 at 9:29 .

It seems that this question does not correspond to the subject of the site. Those who voted to close it indicated the following reasons:

  • "The question is caused by a problem that is no longer reproduced or typed . Although similar questions may be relevant on this site, solving this question is unlikely to help future visitors. You can usually avoid similar questions by writing and researching a minimum program to reproduce the problem before publishing the question. " - Bald, aleksandr barakin
  • “Questions asking for help with debugging (“ why does this code not work? ”) Should include the desired behavior, a specific problem or error, and a minimum code for playing it right in the question . Questions without an explicit description of the problem are useless for other visitors. See How to create minimal, self-sufficient and reproducible example . " - Denis, Kromster, cheops
If the question can be reformulated according to the rules set out in the certificate , edit it .

  • 2
    ON must be after each JOIN and not in brackets at the end ...... JOIN students ON groups.id = students.gr_id JOIN (SELECT DISTINCT st_id FROM marks) AS der_table ON students.id = der_table.st_id is at least - Alexey Shimansky
  • Why don't you want to give the error code and text? - Kromster
  • @ Alexey Shimansky, thank you - Muscled Boy

1 answer 1

Because you wrote the sql query incorrectly. The connection of several tables occurs according to the principle:

 table1 t1 JOIN table2 t2 ON t1.id = t2.id JOIN table3 t3 ON t1.id = t3.id /* и т.д. */

Thus, your request will look like:

 SELECT COUNT(DISTINCT groups.id) FROM groups JOIN students ON groups.id = students.gr_id JOIN (SELECT DISTINCT st_id FROM marks) AS der_table ON students.id = der_table.st_id

Or you can get rid of the second join of the tables and make a subquery:

 SELECT COUNT(DISTINCT groups.id) FROM groups JOIN students ON groups.id = students.gr_id WHERE students.id IN (SELECT st_id FROM marks)
  • Tell me, and IN in this case, as if indicates that the id from the table of tables is also the same as the st_id from marks? And they say that thanks to the subquery the query itself will be faster, and after all all three tables will still be connected ????? - Muscled Boy
  • one
    @MuscledBoy is not, with the subquery will run slower, but if there is little data, you will hardly see the difference. As for in , in order not to go into details, it's better to read here - sql-tutorial.ru/ru/book_predicate_in.html - Denis
  • @Demis, thanks for the reply, thanks for the link and for a clear explanation - Muscled Boy