SQL Query help needed

[DawnM]

I am taking a database course and we are required to create some SQL queries. So don't give me the answer if possible. Just let me know what is wrong with this code:

SELECT
p.pat_id,
p.pat_first_name,
p.pat_last_name,
p.pat_dob,
ph.phys_last_name,
s.service_desc,
v.visit_date
FROM Visit as v
LEFT OUTER JOIN Visit_Service as vs on vs.visit_id = v.visit_id
LEFT OUTER JOIN Patient as p on p.pat_id = v.pat_id
LEFT OUTER JOIN Service as s on s.service_cd = vs.service_cd
LEFT OUTER JOIN Physician as ph on ph.phys_id = v.phys_id
WHERE v.pat_id = '101'
GROUP BY p.pat_id
HAVING v.visit_date = MAX(v.visit_date)

Thanks so much, Dawn

[stbuchok]

It really kind of depends. What is it supposed to do and what is it actually doing?

[debasisdas]

since you are using GROUP BY clause , you need to use some grouping function as part of the select list.

[DawnM]

I'm trying to pull information from the visit table about the most recent visit. The most recent visit might have more than one record. I want all records to display.

[debasisdas]

I want all records to display.

Then why you need to use GROUP BY clause in the SQL ?

[crishlay]

you are using left outer join so there may be possible one of its dependency rows not getting while select loop in query