[RESOLVED] GROUP BY without declare field in SELECT?

[bathurst_guy]

Hi,

I'm just working on an Assignment for Uni. Is it possible to GROUP BY something that isn't declared in the SELECT statement.

For example:
My Assignment Question is this:
List the names of all students who attended an event that was attended by a representative of the company "Perception".

My SQL so far:
SELECT `STUDENT`.`stu_fname` , `STUDENT`.`stu_lname` , `STUDENT`.`stu_no`
FROM `STUDENT` , `REP_ATTENDANCE` , `COMPANY` , `REPRESENTATIVE` , `STUDENT_ATTENDANCE`
WHERE `COMPANY`.`co_name` = 'Perception'
AND `REPRESENTATIVE`.`co_id` = `COMPANY`.`co_id`
AND `REP_ATTENDANCE`.`rep_id` = `REPRESENTATIVE`.`rep_id`
AND `STUDENT`.`stu_no` = `STUDENT_ATTENDANCE`.`stu_no`
GROUP BY `STUDENT`.`stu_no` Which returns the correct information: Carolyn Lodge 00002563
Joseph Witney 00005546
Jessica Wood 00008512
Daisy Blackmore 00250050
Sue Wilson 00501158
Iva Sawpit 02515400
Fred Frog 02551024
Robert Moffatt 05150025
Jenny Hammond 11051869
Ronald Smith 21554621
Ima Idjit 23515421
Danielle Blackmore 51892154
Errol Curry 55564444
Marie Chopping 87898887
Only problem is it also then returns the stu_no next to each students names, can I have it GROUP BY stu_no but not display it as an output? If I remove `STUDENT`.`stu_no` from the SELECT statement then it doesn't work either. Maybe there is a completely different way I should be doing this, with JOIN maybe? I don't understand JOIN yet though...

[mattyblah]

i think you're missing something. is there an event table? or is there anyway to link the events attended by reps to events attended by students?

[bathurst_guy]

There is an events table but it doesn't matter what event they attended, I just need to know if the student and the company representative attended the same event, which I can find out via the student_attendance and rep_attendance tables.

As I said, the SQL above produces the correct output, except it also outputs teh stu_no which I don't want, the lecturer just wants the students names. But I can't group by either of their names as students can have the same first name or the same surname and I don't want multiple students grouped together. Therefore I need to group by the primary key, stu_no, but I don't want it to be in the result of the query.

[chazzy]

There's no reason why you can't. To be honest though - you need to list each column you're selecting in your group by. MySQL uses a short cut syntax to skip it, but all other DBMS require it. When you say it didn't work, did it give you an error?

[bathurst_guy]

:o OK So I just tried it again without declaring the stu_no in SELECT and it worked....

So Chazzy, would my lecturer mark me down if I don't include it due to other DBMS requiring it? As we are learning "SQL" not "MySQL".

[chazzy]

I dunno, I can't speak for your lecturer. I remember in my courses we learned off of MySQL, but each answer had to be ansi compliant (it was kind of a way to keep us from easily testing our code).

[mattyblah]

Can you even explain why your results are correct? I'd at least use exists so you can test equality and I'm guessing you instructor will ask you why you used the method you did.

[bathurst_guy]

What's exists and how is it used?