Advanced SQL HELP

[shmick25]

Ok Guys

whether or not this is really advanced is ramained to be seen, however, I can't get it going without writing additional code and maybe an SP.

Here is the sql that works to a degree:

SELECT u.user_id,u.dtcreated,u.user_name,u.firstname, u.lastname,s.filelocation,s.processed, p.program FROM users AS u
INNER JOIN student_details s ON (u.user_id = s.user_id)
LEFT OUTER JOIN stu_prog_status sp ON (u.user_id = sp.user_id)
LEFT OUTER JOIN programs p ON (sp.program_id = p.program_id)
WHERE (s.processed = 'N') AND (user_role = '6') ORDER BY sp.enrol_date DESC

the problem is that it returns 3 results per student when I only want one result. This is due to the fact that stu_prog_status is a table that tracks a students history in a program.

ie. If there is one record then I in stu_prog_status I will get one result form the above query, if I have 5 records then I will get 5 results.

The thing is I only want it to return the one results: The program he is currently enrolled in, not all his privious ones. How do I do this? Is there anyway?

Hope it isnt too confusing

[zhabala]

Are you able to differentiate the record in stu_prog_status ? You should add something like

(u.user_id = sp.user_id AND sp.open = 'TRUE' ) or you can use the DISTINCT like this:

SELECT DISTINCT u.user_id,u.dtcreated,u.user_name,u.firstname, u.lastname,s.filelocation,s.processed, p.program FROM users AS u
INNER JOIN student_details s ON (u.user_id = s.user_id)
LEFT OUTER JOIN stu_prog_status sp ON (u.user_id = sp.user_id)
LEFT OUTER JOIN programs p ON (sp.program_id = p.program_id)
WHERE (s.processed = 'N') AND (user_role = '6') ORDER BY sp.enrol_date DESC