How to display the desired rows of two tables using a subquery?-CodePudding

My subquery:

select studentName, Course.dataStart
from Student,
     Course
where Student.id in (select Course.id from Course);

I need a solution to this (above) subquery (not a join)

Why does the SQL subquery display one date for each name? (task: display the names of students from the Student table and the course start date from the Course table using a subquery)

With the help of Join, I get it as it should: (but I need to do it with a subquery)

CodePudding user response：

You seem to be using implicit join syntax, but really you should be using an explicit inner join:

SELECT s.studentName, c.dataStart
FROM Student s
INNER JOIN Course c
    ON c.id = s.course_id;

If you really wanted to use the implicit join syntax, it should be something like this:

SELECT s.studentName, c.dataStart
FROM Student s, Course c
WHERE c.id = s.course_id;

But again, please use the first version as its syntax is considered the best way to do it.

CodePudding user response：

You can apply join :

SELECT S.studentName, C.dataStart
FROM Student S
INNER JOIN Course C
ON C.id = S.course_id;

With Sub query:

Select studentName, (Select Course.dataStart from Course 
Where  Course.id = course_id)
From Student

CodePudding user response：

Asuming that Course.Id field is Student.Id (although it seems strange to me), I think the only way to get the results you want with a subquery would be using it in the SELECT clause:

select studentName, (SELECT Course.dataStart FROM Course WHERE Course.Id = Student.Id)
from Student

This would fail if you have more than 1 row in Course per Student, in that case you could use (SELECT DISTINCT Course.dataStart...)