Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts

    Sorting a relational set

    I have three tables in a contact management system that I'm using. The first is the prmContacts with First, Last, and other info. The second is prmFollowup which references the prmContactID and contains a comments and appointment field. The third is a prmInfo which contains company name and active/inactive status.

    I need to generate a repeating list that contains First Name, Last Name, Company Name, and appointment fields. I've got evertything worked out except I can't seem to make the displayed info pull up the latest appointment field.

    Here's my SQL query.
    PHP Code:
    $query_activeContactsRS "SELECT prmContacts.id, prmContacts.custFname, prmContacts.custLname, prmInfo.companyName, prmFollowup.nextAppointment, prmFollowup.nxtTime 
    FROM prmContacts, prmInfo, prmFollowup 
    WHERE prmContacts.id =prmFollowup.prmContactID AND prmInfo.prmContactID =prmFollowup.prmContactID AND prmContacts.custActive = 'Yes' 
    GROUP BY prmContacts.id DESC 
    ORDER BY prmFollowup.id DESC"

    Let's talk about the query. Matching up the contact with the Customers and the prmFollowup is perfect. The GROUP BY prmContacts.id prevents duplicate entries for each prmContact.id so I end up with a list of only my contacts. Without the GROUP BY method I get with a new row for each prmFollowup field. What I want is just the last followup record displayed for each contact name. With this query I'm displaying the first followup row with each name.

    If anybody has any ideas about how to make this work please let me know. At this point the only work around I can think of involves linking to another page to display the next apporintment for the contact.
    Last edited by rgEffects; 10-16-2012 at 03:18 PM.

  • #2
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,647
    Thanks
    2
    Thanked 406 Times in 398 Posts
    What is the structure of prmFollowup? If you have an auto-increment key or date you can use MAX(prmFollowup.id) in the SELECTed fields to get the last record in a joined table. You could also do a subquery and other less-desirable options.

  • #3
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts
    MAX(prmFollowup.id) does deliver the ID of the latest nextAppointment row for each name but it does not sort the combined records. Changing the query to MAX(prmFollowup.nextAppointment) gives me the next appointment date for each record unless the appointment is moved up.

    I think this may be somewhat on the right track but what I really need is to deliver the Group (no duplicate names) with the corresponding records for the last appointment made by followup ID.

    I'm ending up with this:

    First Name, Last Name, Company, Next Appointment, Max(prmFollowup.id)
    Bob, Jones, Company 1, 10/21/12, 15
    Tom, Thumb, Company 2, 10/26/12, 9

    But the appointment date for id #15 is 10/20/12 so the displayed appointment date is wrong. Both displayed dates from the query are the first nextAppointment field matching the contact ID. Both MAX(prmFollowup.id)'s are correct for the contacts.

    The solution looks like it needs to sort by prmFollowup.id first then group by prmContacts.id Unfortunately sorting then grouping produces an error.
    Last edited by rgEffects; 10-15-2012 at 05:47 AM.

  • #4
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,647
    Thanks
    2
    Thanked 406 Times in 398 Posts
    Post the SQL needed to create the tables you are joining on and some sample(like the two users and a few appointments) data so I or someone else can help you with the query.

  • #5
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts
    Here's the table prmContacts

    id(primary key), date (timestamp), custFname, custLname, other stuff...

    Here's the table prmFollowup

    id(primary key), date (timestamp>, prmContactID, nextAppointment, other stuff.

    Sample data for prmContacts

    1, (timeStamp), Bob, Jones .... other stuff
    2, (timeStamp), Tom, Thumb ..... other stuff

    Sample data for prmFollowup

    1, (timeStamp), 1, 10/20/12 .... other stuff
    2, (timeStamp), 1, 10/17/12 .... other stuff
    3, (timeStamp), 2, 10/18/12 .... other stuff
    4, (timeStamp), 1, 10/19/12 .... other stuff
    5, (timeStamp), 2, 10/25/12 .... other stuff

    Here's my entire PHP
    PHP Code:
    mysql_select_db($database_civTekDB$civTekDB);
    $query_activeContactsRS "SELECT prmContacts.id, prmContacts.custFname, prmContacts.custLname, prmInfo.companyName, prmFollowup.nextAppointment, prmFollowup.nxtTime FROM prmContacts, prmInfo, prmFollowup WHERE prmContacts.id =prmFollowup.prmContactID AND prmInfo.prmContactID =prmFollowup.prmContactID AND prmContacts.custActive = 'Yes' GROUP BY prmContacts.id DESC ORDER BY prmFollowup.id DESC";
    $activeContactsRS mysql_query($query_activeContactsRS$civTekDB) or die(mysql_error());
    $row_activeContactsRS mysql_fetch_assoc($activeContactsRS);
    $totalRows_activeContactsRS mysql_num_rows($activeContactsRS); 
    This is just the query (formatted to be easier to read)

    SELECT prmContacts.id, prmContacts.custFname, prmContacts.custLname, prmInfo.companyName, prmFollowup.nextAppointment, prmFollowup.nxtTime
    FROM prmContacts, prmInfo, prmFollowup
    WHERE prmContacts.id =prmFollowup.prmContactID AND prmInfo.prmContactID =prmFollowup.prmContactID AND prmContacts.custActive = 'Yes'
    GROUP BY prmContacts.id DESC
    ORDER BY prmFollowup.id DESC

    This is what I get from the query:

    record, custFname, custLname, nextAppointment, .... other stuff
    1, Tom, Thumb, 10/18/12 ... other stuff
    2, Bob, Jones, 10/20/12 ... other stuff

    The first next appointment date is shown. What I want to display is this:
    1, Tom, Thumb, 10/25/12 .... other stuff
    2, Bob, Jones, 10/19/12 .... other stuff

    If I add MAX(prmFollowup.id) to the Selected and display it I get this:
    record, custFname, custLname, nextAppointment, MAX(prmFollowup.id)
    1, Tom, Thumb, 10/18/12, 5
    2, Bob, Jones, 10/20/12, 4

    This was actually what I expected because I was just calling up the last followup id, I was not sorting the records.

    I hope somebody has some ideas. My work around is not only slow, it's cumbersome.
    Last edited by rgEffects; 10-15-2012 at 06:01 PM.

  • #6
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts
    Let me try simplifying the problem even more. Bob and Tom change their favorite color.

    Here's the database in comma deliminted form. The id field is the primary key. I'm trying to simply return unique contact id's (that part is easy with group) and list the last entry for each user.

    prmFollowup (table data) field names in first row.

    id, contact, favColor
    1, Bob, red
    2, Tom, green
    3, Tom, red
    4, Bob, blue

    Here's the first PHP Query

    SELECT contact, favColor
    FROM prmFollowup
    GROUP BY contact
    ORDER BY id DESC

    Here is the result: prmCustID in order and the first record ID for favorite color.

    Bob, red
    Tom, green

    No duplicate customer ID's, as expected, but the favorite colors are out of order. ORDER BY id isn't working because of the GROUP function.

    Take out the GROUP BY method and I get the proper order for favorite colors, latest color choice first, but I have duplicates

    Bob, red
    Bob, green
    Tom, blue
    Tom, red

    What I need is the just the last favColor for each contact.
    Bob, red
    Tom, blue

    I've tried a bunch of options including HAVING and DISTINCT with no success. This has got to be a commonly needed query for a relational database. Any help would be appreciated.
    Last edited by rgEffects; 10-15-2012 at 11:39 PM.

  • #7
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts
    I got it worked out with some help. Turns out if you use brackets to isolate a where method and use IN you can group and sort. The simplified solution looks like this:

    SELECT contact, favColor
    FROM prmFollowup
    WHERE ID in
    (SELECT Max(ID)
    FROM prmFollowUp
    GROUP BY contact)


  •  

    Posting Permissions

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