Second query within a query using results of first query

Question

The following select statement returns a list of students:

SELECT * FROM students2014 ORDER BY LastName

However, for each row I need to return data from another table (notes2014) to allow me to display the latest note for each student. The select statement would be as follows:

SELECT Note FROM notes2014 WHERE NoteStudent='$Student'

$Student indicates the ID for each student in the students2014 database. However this result only appears after the initial statement is queried.

So my question is, how can I run the second query within the first?

You probably need a join.

Mihai
– Mihai

2013-10-23 09:24:33 +00:00
Commented Oct 23, 2013 at 9:24 — Mihai
– Mihai, Commented Oct 23, 2013 at 9:24

halfer · Accepted Answer · 2014-02-24 22:38:04Z

1

Posted on behalf of the OP:

I've worked it out! Thank you for you help. I used left join in the end:

SELECT * FROM students2014
    LEFT JOIN notes2014 ON students2014.Student = notes2014.NoteStudent
    WHERE students2014.Consultant='$Consultant'
    ORDER BY students2014.LastName

answered Feb 24, 2014 at 22:38

community wiki

halfer

Sign up to request clarification or add additional context in comments.

Comments

웃웃웃웃웃 · Accepted Answer · 2013-10-23 09:29:26Z

0

you should query the data like this:

select students2014.*, notes2014.note from students2014, notes2014 where students2014.id = notes2014.NoteStudent

edited Oct 23, 2013 at 9:29

웃웃웃웃웃

12k15 gold badges63 silver badges95 bronze badges

answered Oct 23, 2013 at 9:26

roeb

5078 silver badges19 bronze badges

1 Comment

Phil Howell Over a year ago

That works! Thank you! However the results exclude any students that don't presently have any corresponding notes... I need to display a complete list even if some students don't currently have a note.

Sashi Kant · Accepted Answer · 2013-10-23 10:40:09Z

0

Try this::

Select 
Student, 
Note 
from 
students2014 s
INNER JOIN notes2014 n on Student = n.NoteStudent
ORDER BY lastName

edited Oct 23, 2013 at 10:40

answered Oct 23, 2013 at 9:24

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

3 Comments

Phil Howell Over a year ago

I'm afraid I'm totally lost with this. I've tried it and I'm getting absolutely nothing. What do you mean by studentId and student_ID? The column that indicates student ID is Student.

Sashi Kant Over a year ago

@PhilHowell: what is the studentId column in student2014?

Phil Howell Over a year ago

It's called Student :) It's just an incremental value.

Collectives™ on Stack Overflow

Second query within a query using results of first query

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related