SQL query help

Hi all

I know this isn't asp...but i hope someone can still help

I have this query, but instead of returning the most recent permission for each hcp, it's just returning the most recent permissions!!

SELECT hcp.HCP_ID, Lastname, Firstname,product, permission_type.Type_Name, Date_Entered FROM hcp, permission_type, permission_details, permission_header WHERE hcp.HCP_ID=permission_header.hcp_id AND permission_header.Permission_ID=permission_details .permission_id AND permission_details.Type_Name=permission_type.type_ name and permission_details.type_name<> 'Withdrawn' AND permission_details.Date_Entered=(select max(permission_details.date_entered) from permission_details,hcp,permission_header where permission_details.permission_id=permission_header .permission_id and permission_header.hcp_id=hcp.hcp_id) ORDER BY hcp.Lastname

Guest

I'm sorry i don't understand what your trying to achieve. Can you clarify please?

Was this answer helpful ? Yes No

Guest

Please don't post the same question in multiple forums.

Was this answer helpful ? Yes No

Guest

It wasn't intentional...sorry!!

Basically, I want to select from permission_details, the most recent permission per hcp. Currently, my query returns everything from permission_details where the date is the most recent...irrespective of HCP....does that help?? So at the moment, I'm getting all the permissions which were entered today...hence multiple rows for hcp's instead of just one??

Hope that helps!!

Was this answer helpful ? Yes No

Guest

Just shooting from the hip here, but try adding DISTINCT to your query:

Code:

SELECT DISTINCT hcp.HCP_ID, Lastname, Firstname,product, permission_type.Type_Name, Date_Entered FROM hcp, permission_type, ....etc .

Was this answer helpful ? Yes No

Guest

I thought of that!! Nah...didn't work!! I'm positive it linked to the max part....just not sure how!!

Was this answer helpful ? Yes No

Guest

It would probably return the most recent permission per hcp with this query, but I don't know if you need more detail than just the hcp names

Code:

SELECT DISTINCT hcp.HCP_ID, Lastname, Firstname FROM hcp, permission_type, permission_details, permission_header WHERE hcp.HCP_ID=permission_header.hcp_id AND permission_header.Permission_ID=permission_details   .permission_id AND permission_details.Type_Name=permission_type.type_   name and permission_details.type_name<> 'Withdrawn' AND permission_details.Date_Entered=(select max(permission_details.date_entered) from permission_details,hcp,permission_header where permission_details.permission_id=permission_header   .permission_id and permission_header.hcp_id=hcp.hcp_id) ORDER BY hcp.Lastname

If this at leasts limits the results, you can start adding extra fields to the query until you have enough information.

Was this answer helpful ? Yes No

Guest

That's strange! I'm getting an error message that says missing operator...exactly the same error I got when I tried someone else's attempt at this!! Could it be an error with the DB, or the fields?

Was this answer helpful ? Yes No

Guest

Check that there are no extraneous spaces in the SQL query. This can happen if you copy & paste from the posted code, because this dumb forum software adds spaces to strings which exceed a certain length. You'll see an example of this behaviour in the code block I posted in post #7.

Was this answer helpful ? Yes No

Guest

Answer it

Ask Question About ASP Development

SQL query help

Programming

Related Pages