SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)

    Avoiding the split of a query.

    I'm fetching a patient with this query.

    PHP Code:
    $patient PAMWF::$db->queryFirstRow("
        SELECT patients.recordid AS patientid, lastname, firstname, middlename, 
        birthdate, gender, legacyid, married,
        street, section, city, 
        addresses.state AS state, 
        zipcode, route, number
    FROM patients
        JOIN personal ON (patients.parentid = personal.recordid)
        JOIN addresses ON (addresses.parentid = personal.recordid AND addressType = 'P')
        JOIN phone ON (phone.parentid = personal.recordid AND phone.isPrimary = 1)                    
    WHERE patients.recordid = 
    {$_SESSION['patients']['recordid']}
    GROUP BY patients.recordid
    LIMIT 1"
    ); 
    If the patient has no primary phone number the query returns an empty result. I still need to display the address and patient name in this event. This can also be caused by the address information being incomplete although that shouldn't normally arise due to validation by the php script as the data is gathered.

    So, is there a way to avoid splitting this query into two calls (one for the patient data, one for phone number)?

    (I'll split it for now, but I'm curious to see if it can be done.)

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Use a LEFT OUTER JOIN instead of the implicit INNER JOIN here. A LEFT OUTER JOIN will return all rows of the table on the left side of the join even if there is no match in the right side of the join.

  3. #3
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    Thank you!!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •