To say that your query is "never going to look like that" isn't entirely true. I'm sure with enough CASE statements and the use of some variables and such you could get your SQL output to look like that. SQL is pretty powerful like that.
BUT.. Everyone who said "Use PHP to filter/display the data" is correct. That's probably the preferred method of accomplishing this goal. It's how I would do it at least. I don't think SQL is the best for doing custom display of data, you just need to learn what data you're getting back from the SQL statement and what you need to do to it in order to get the information that you need and display it how you want to. Someone else mentioned the "Group By" not containing one of the fields, that's a good call, but I'm wondering why "Group By" is used here at all. There are no aggregate functions like SUM or MAX or anything, so Group By isn't needed. If for some reasons you were getting duplicate records, the Group By would elimate them (I believe) in this case, but a SELECT DISTINCT ... Would do the same thing. Actually, Group By wouldn't need to contain any/all of the SELECT fields because there's no aggregate, so you can probably do any number of the SELECT items in the Group By without an error. No aggregate means no Group By required so you could do one, three, fifty items.. Doesn't matter. Shouldn't produce an error, it'll just do a selective DISTINCT instead of a total DISTINCT. Sounds like GH thinks that "Group By" is a function that will create the output that he's looking for. It's not really used for that. It's used for telling SQL how to group data that's not part of the MAX or SUM or AVG or other aggregate functions. If you did: SELECT A.`AttID` , MAX(S.`SessionDate`) , P.LastName, P.FirstName, A.`Present` FROM `Attendance` A, Sessions S, Participants P WHERE S.SessionID = A.`Session` AND P.Part_ID = A.`Participant` GROUP BY A.`AttID`, P.LastName, P.FirstName, A.Present, A.AttID This will give, for each name and unique SessionDate, you one AttID, one Last Name, one First Name, one Present... All for the latest SessionDate. Because we're looking at the max SessionDate (greatest value) and returns the info for that date value. If someone attended more than one Session on the same day, it'll return more than one line for that person with different AttID's. Just some random thoughts. -TG > -----Original Message----- > From: John Holmes [mailto:[EMAIL PROTECTED] > Sent: Saturday, November 20, 2004 10:22 AM > To: GH > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: [PHP-DB] Optimize Query Output > > > GH wrote: > > I have the following query: > > > > SELECT A.`AttID` , S.`SessionDate` , P.LastName, > P.FirstName, A.`Present` > > FROM `Attendance` A, Sessions S, Participants P > > WHERE S.SessionID = A.`Session` AND P.Part_ID = A.`Participant` > > GROUP BY P.LastName, P.FirstName, A.Present, A.AttID > > > > I would like to have the output to have the P.LastName and > P.FirstName > > values only shown once and the rest of the output printed... > > > > So instead of something like: > > > > | 1 | 2004-10-30 | Apple | Robert | Yes | > > | 11 | 2004-11-06 | Apple | Robert | Yes | > > > > To have it look like: > > > > | Apple | Robert S. | 1 | 2004-10-30 | Yes | > > | | | 11| 2004-11-06 | Yes | > > | | | 31| 2004-11-13 | Yes | > > | Bravo | Luz | 2 | 2004-10-30 | Yes | > > | | | 32| 2004-11-06 | No | > > Your query is never going to look like that. You use PHP to > format the result set so it displays how you want it. As you > loop through the returned rows, keep track of what the current > first and last name are. > If they change, then output them, otherwise output a blank cell. > > -- > > ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
