如何通过MySQL查询展示特定患者记录及关联表多条件查询

Hey there! Let's tackle your two MySQL requirements clearly and practically:

To pull up records for a particular patient, you'll want to filter using a unique identifier (like a patient ID) or their name (note: names might not be unique, so IDs are more reliable). Here are some common examples:

• By Patient Name:

  SELECT * FROM patient_record
  WHERE patient_name = 'John Doe'; -- Replace with your target patient's name
  

• By Unique Patient ID (recommended to avoid name duplicates):

  SELECT * FROM patient_record
  WHERE patient_id = 'PAT-00123'; -- Replace with the patient's unique ID
  

• Add a Time Range Filter (e.g., records from 2024):

  SELECT * FROM patient_record
  WHERE patient_name = 'John Doe' 
    AND date_time BETWEEN '2024-01-01 00:00:00' AND '2024-12-31 23:59:59';
  

Based on your table structure, we'll use LEFT JOIN to link patient_record (shortened to pr) with patient_checkup (pc) and patient_medicine (pm), using the code field from patient_record as the common link (adjust the join fields if your tables use a different key like record_code).

This query will show core record details (code, patient name, date) alongside all related checkup and medicine data:

SELECT 
  pr.code AS record_code,
  pr.patient_name,
  pr.date_time,
  -- Include fields from patient_checkup that you need
  pc.checkup_type,
  pc.checkup_result,
  -- Include fields from patient_medicine that you need
  pm.medicine_name,
  pm.dosage_instructions
FROM patient_record pr
LEFT JOIN patient_checkup pc ON pr.code = pc.record_code -- Adjust if your foreign key name differs
LEFT JOIN patient_medicine pm ON pr.code = pm.record_code
-- Optional: Add a filter for a specific patient
-- WHERE pr.patient_name = 'John Doe'
ORDER BY pr.date_time DESC; -- Sort by most recent records first

Quick Notes on the Join: #

• Use LEFT JOIN instead of INNER JOIN to ensure you still see the base patient record even if there's no associated checkup or medicine data. If you only want records that have both checkup and medicine entries, switch to INNER JOIN.
• Replace the example fields (like checkup_type, medicine_name) with the actual column names from your tables.

内容的提问来源于stack exchange，提问作者muneeb