MySQL Query distinct records

I need help with a MySQL query I have the following query

SELECT Class.class_id, class.subject_id, class.teach_id, class.tcode, subjects.aca_lvl, subjects.name FROM subjects, class, faculty WHERE subjects.sub_id = class.subject_id AND subjects.fac_id = faculty.FacID and faculty.hod = 2

and this works well. I would like to expand on this query and if class.subject_id is repeated in multiple records I would just like to return the distinct records, i.e. one unique class.subject_ID

anyone got any idea how I might do this???

Thanks

Jake
elschottAsked:
Who is Participating?
 
Cornelia YoderConnect With a Mentor ArtistCommented:
SELECT Class.class_id, class.subject_id, class.teach_id, class.tcode, subjects.aca_lvl, subjects.name FROM subjects, class, faculty WHERE subjects.sub_id = class.subject_id AND subjects.fac_id = faculty.FacID and faculty.hod = 2 GROUP BY class.subject_ID
0
 
NicksonKohCommented:


Hi elschott,

If there are duplicate results, you can simply do a distinct on the sql as follows

SELECT Distinct Class.class_id, class.subject_id, class.teach_id, class.tcode, subjects.aca_lvl, subjects.name FROM subjects, class, faculty WHERE subjects.sub_id = class.subject_id AND subjects.fac_id = faculty.FacID and faculty.hod = 2

If that's not the case, can you give a simple example to illustrate ur point.

yodercm, I am afraid that SQL you have provided is incorrect.

Cheers,
NicksonKoh
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
@NicksonKoh: actually, with MySQL, the GROUP BY subjectid as suggested by yodercm WILL work, in contrast with all other database products...
0
 
elschottAuthor Commented:
The group by function did work as I had hoped for my particular need so I will go with it, thanks to everyone who contributed

Jake
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.