Two tables:

  • parents: id, name, surname, job;
  • students: id, name, surname, gr_id, m_id, f_id;

It is necessary to enter information about the work of Smirnov’s student’s parents (the use of a subquery is mandatory since the topic is “simple subqueries”). I tried this:

SELECT * FROM parents WHERE id IN (SELECT m_id, f_id FROM students WHERE surname IN ('Smyrnov')); // здесь пишет операнд должен иметь один столбец SELECT * FROM parents WHERE id IN (SELECT m_id AND f_id FROM students WHERE surname IN ('Smyrnov')); // понимаю,что поля выборки в подзапросе не перечисляют через "AND", но все же. Получил информацию о матери студента SELECT * FROM parents WHERE id IN (SELECT m_id FROM students WHERE surname IN ('Smyrnov')) AND (SELECT f_id FROM students WHERE surname IN ('Smyrnov')); // то же, что и в предидущем случае
  • so how is it right to do? - Muscled Boy
  • You need the subquery to return 2 different fields as separate rows. This is usually done with the help of UNION - Mike
  • with UNION did, but didn’t catch the right answer - Muscled Boy

1 answer 1

With the request

  SELECT * FROM parents WHERE id IN (SELECT m_id FROM students WHERE surname='Smyrnov') OR id IN (SELECT f_id FROM students WHERE surname='Smyrnov');
  • it works, only we could not explain why the condition "AND" - "empty set" - Muscled Boy
  • 2
    @MuscledBoy select X from tab where id=A and id=B condition is applied in turn to each record. And it turns out that in the same record id it is difficult to be equal to A and simultaneously equal to B. And the correct condition id equals A OR equals B - Mike
  • @Mike, but everything is really logical. Thanks for the reply - Muscled Boy