How do I combine multiple queries into an array?

Question

I have two tables set up. One for student information, one for student/class listing. I'm coding in PHP using the sqlsrv plugin for MSSQL 2008 R2.

Student ID
Class Code

Student ID
First Name
Last Name

I need to select all students from a class but order by last name.

SELECT student_id FROM table1 WHERE class_code LIKE '402843'
$students = SELECT last_name, first_name FROM table2 WHERE student_id LIKE 'all results of first query'

Right now I can select all the students and display all the information; however, I can not sort them by last name. I'm sure this is possible, but seeing that the first query will return an array, I need the second query to return an array as well.

Any help would be greatly appreciated.

score 3 · Answer 1 · answered Sep 05 '12 at 18:12

look at joins in SQL and do it all in your query rather than in PHP...

using your examples and for the purposes of code, naming the tables as follows

ClassCodeTable  
    Student ID
    Class Code

StudentInfoTable
    Student ID
    First Name
    Last Name

then your query would look like

SELECT SI.last_name, SI.first_name FROM StudentInfoTable SI JOIN ClassCodeTable CC on CC.student_id=SI.student_id WHERE CC.class_code LIKE '402843' ORDER BY SI.last_name ASC

Mihai Iorga · Accepted Answer · 2012-09-05T18:13:44.140

1

Use IN subquery:

SELECT last_name, first_name FROM table2 WHERE student_id IN (SELECT student_id FROM table1 WHERE class_code='402843') ORDER BY last_name ASC

edited Sep 05 '12 at 18:13

answered Sep 05 '12 at 18:05

Mihai Iorga

39,330
16
106
107

That worked like a charm. Thank you very much... the 'in' is new to me, appreciate the help! Wish I could give you a thousand upvotes. – Fleppar Sep 05 '12 at 18:21
No, but It's more complicated and I don't really like joins, as much simplified as possible, I'll give him +1 :) – Mihai Iorga Sep 05 '12 at 18:23

How do I combine multiple queries into an array?

2 Answers2