Question 112998 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql get value from max of two rows

I have a table called relationships:
relationships

And another called relationship_type:
relationship type

A person can have a social worker assigned to them on a specified time period (has start and end time), or specified to them with a start time only. I am writing a query to retieve records of a social worker assigned to someone during a filtered time.
My query looks like this :

SELECT r.person_a AS patient_id, greatest((r.start_date), (r.end_date)) as relationship_date,
concat_ws( ' ', pn.family_name, pn.given_name, pn.middle_name ) AS NAME
FROM
relationship r 
INNER JOIN relationship_type t ON r.relationship = t.relationship_type_id
INNER JOIN person_name pn ON r.person_b = pn.person_id 
WHERE
t.uuid = '9065e3c6-b2f5-4f99-9cbf-f67fd9f82ec5' 
AND (
r.end_date IS NULL 
OR r.end_date <= date("2022-10-26"));

I only want to retrieve the patient_id of the user and name of case worker whose relationship is valid during the filtered time. In an instance where a relationship has no end date, i use the start date. Any advice/recommendation on what i am doing wrong will be appreciated.

My current output :
output

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    This helped me out :

    SELECT t.patient_id, t.latest_case_manager from (SELECT r.person_a AS patient_id, mid(max(concat(date(r.start_date)," +
                " concat_ws( ' ', pn.family_name, pn.given_name, pn.middle_name ))), 11) as latest_case_manager, max(r.start_date)" +
                " as start_date FROM relationship r INNER JOIN relationship_type t ON r.relationship = t.relationship_type_id " +
                "INNER JOIN person_name pn ON r.person_b = pn.person_id WHERE t.uuid = '9065e3c6-b2f5-4f99-9cbf-f67fd9f82ec5' " +
                "and date(r.start_date) <= ("2022-10-30") GROUP BY patient_id having date(start_date) <= ("2022-10-30")) as t;

  2. 0

    If I’m understanding your question, and you just want patient_id and NAME showing up and not the start_date and end_date, then just remove them from the select statement. The select statement is the stuff you want to show up. You don’t need to include the dates just to use them in your WHERE statement.

    SELECT r.person_a AS patient_id,
concat_ws( ' ', pn.family_name, pn.given_name, pn.middle_name ) AS NAME
FROM
relationship r 
INNER JOIN relationship_type t ON r.relationship = t.relationship_type_id
INNER JOIN person_name pn ON r.person_b = pn.person_id 
WHERE
t.uuid = '9065e3c6-b2f5-4f99-9cbf-f67fd9f82ec5' 
AND (
r.end_date IS NULL 
OR r.end_date <= date("2022-10-26"));
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search