• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1749
  • Last Modified:

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
0
elschott
Asked:
elschott
1 Solution
 
Cornelia YoderArtistCommented:
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now