multiple table join - postgresql, VERY rusty

[dc dalton]

One of my contractors was supposed to write queries for a big project Im doing but disapeared (oh joy) so I am forced to "climb back into" the heavy sql part of this thing (normally I do the front end, java and some minor sql as needed). I will tell you my high powered sql is REALLY rusty and its showing today as I cant get anything back on the queries Im trying. Here's what Im TRYING to do:

I am pulling back rows of people (7 fields per row) from a main candidate table which has a unique ID per row. Then I need the max date from another table where multiple rows of comments are stored for a given candidate (to order the candidates in the output by "last updated") AND I need one field from a work experience table, it being the last job position listed in that table for the candidate, hence I have to get the postition for the max (end_date) of their work experience and then get THAT row's job title.

SO the pseudeocode should go like this: (at least I think!)

select
required fields from candidates (includes unique id)
max(last_updated) from comments (comment_can_id = candidates_id)
last_position from work_experience where job_end_date is MAX for the specific candidate_id (can_work_id = candidates_id)
order BY last_updated

I should note that the last_updated field for the comments is a timestamp and the job_end_date is a regular Date field

I havent done this nasty of a query in YEARS and BOY how we forget! Ive tried all possbile joins I could think of and am particularly frustrated with the group bys Postgres is requiring ..... got 10 books sitting here and a headache to beat the band! Can someone give me the kick in the head I seem to need (or at least an aspirin)?

thanks much ...... this guy has REALLY put me behind the 8-ball!

[dc dalton]

well finding a MAJOR flaw in the way I had that last updated field sent me in a different direction, which led me to an answer. Just wanted to post back so no one else tried to kill themselves!

I had to add a last_updated field to the main table becuase I realized by having the last_updated on another table (comments) I had limited my ability to know if anything else had been updated (funny how those little glitches come up)

anyways heres the query Im using ....... it works fine but if anyone sees anything dumb performance wise feel free to chime in .. I will mention this app is for private intranet-type apps where traffic is VERY Minimal. Anyways the query:

SELECT c.c_candidate_id, c.c_f_name, c.c_l_name, c.c_city, c.c_state, c.c_areacode, c.c_primary_phone, c.c_resume_filename, c.c_last_updated,
(SELECT w_employer from work_experience where w_end_date=(select max(w_end_date) from work_experience where w_c_id = c.c_candidate_id) AND w_c_id = c.c_candidate_id LIMIT 1) as "employer",
(SELECT w_position from work_experience where w_end_date=(select max(w_end_date) from work_experience where w_c_id = c.c_candidate_id) AND w_c_id = c.c_candidate_id LIMIT 1) as "position"
FROM candidate_info c ORDER BY c.c_last_updated DESC

The one thing that I didnt know (comments appreciated) is that a subselect can only bring back one column????? REALLY??? I didnt know that but Postgres yelled at me BIG TIME when I tried to do two fields from work_experience.

I realize it MAY be a tad messy with the subselects but its going to take me a day or two to get back into the swing of heavy queries like this.