How can I make multiple select statements on the same table?

sql select multiple-select-query
dcp3450 picture dcp3450 · Dec 8, 2010 · Viewed 20.9k times · Source

I have a table that stores some student work info. I need to select the hours based on the studentID and the quarter ID. Here is what I have:

SELECT
(SELECT hours FROM clinicalStudents WHERE quarterID='201101' and studentID='$studentID') as q1,             
(SELECT hours FROM clinicalStudents WHERE quarterID='201102' and studentID='$studentID') as q2,             
(SELECT hours FROM clinicalStudents WHERE quarterID='201103' and studentID='$studentID') as q3,             
(SELECT hours FROM clinicalStudents WHERE quarterID='201104' and studentID='$studentID') as q4

It's only giving me some numbers but not all of them. I ran this (minus the WHERE clause) in my server manager and received an error:

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression"

Any assistance would be great. Thanks!

EDIT:

The $studentID is generated in a while loop so I'm using the hours on that student before I move to the next one. I'm getting all the hours for one student in each quarter, adding them (this has to be done outside of the sql), storing results in a variable then moving to the next student. This works perfect when I get 1 quarter but i'm having an issue getting all the quarters.

EDIT Round 2: Did it in a rather lazy way I suppose:

I just selected all the hours and quarterID's for a specific student. Then ran a while(odbc_fetch_row()). If it was a 201101 I added it to the $q1 pile, 201102 added to the $q2 pile, and so on. Processing is a little slower but not a big issue with what I'm doing.

Answer

Brad Christie picture Brad Christie · Dec 8, 2010

trying use a SELECT TOP 1 or a LIMIT 1 in the query, depending which sql you are running.

EDIT Also, why are you trying to accomplish? This seems clunky and, depending your intended purpose, there is probably a better way available.

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
SQL SELECT WHERE field contains words

I need a select which would return results like this: SELECT * FROM MyTable WHERE Column1 CONTAINS 'word1 word2 word3' And I need all results, i.e. this includes strings with 'word2 word3 word1' or 'word1 word3 word2' …

Read More
SQL update from one Table to another based on a ID match

I have a database with account numbers and card numbers. I match these to a file to update any card numbers to the account number, so that I am only working with account numbers. I created a view linking the …

Read More