...

View Full Version : Resolved Sorting a relational set



rgEffects
10-14-2012, 08:48 PM
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.


$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.

Inigoesdr
10-15-2012, 12:38 AM
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. :)

rgEffects
10-15-2012, 04:44 AM
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.

Inigoesdr
10-15-2012, 03:27 PM
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.

rgEffects
10-15-2012, 04:58 PM
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


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.

rgEffects
10-15-2012, 10:28 PM
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.

rgEffects
10-16-2012, 02:17 PM
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)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum