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
Selecting S.SessionDate can get you in trouble here, as it is not one of your GROUP BY columns. Many systems wouldn't allow this. MySQL does, but the result only makes sense if there is a unique value of S.SessionDate per group. Perhaps that's the case for you. In any event, I don't see why you are using GROUP BY, as you are not using any aggregate functions. Based on your sample desired output, I think you want
ORDER BY P.LastName, P.FirstName, S.SessionDate
rather than GROUP BY.
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:
+-------+-------------+-------------+-----------+---------+ | AttID | SessionDate | LastName | FirstName | Present | +-------+-------------+-------------+-----------+---------+ | 1 | 2004-10-30 | Apple | Robert | Yes | | 11 | 2004-11-06 | Apple | Robert | Yes | | 31 | 2004-11-13 | Apple | Robert | Yes | | 2 | 2004-10-30 | Bravo | Lisa | Yes | | 32 | 2004-11-13 | Bravo | Lisa | Yes | | 12 | 2004-11-06 | Bravo | Lisa | No | | 3 | 2004-10-30 | Beta | Elaine | Yes | | 13 | 2004-11-06 | Beta | Elaine | Yes | | 14 | 2004-11-06 | Delta | Alexander | Yes | | 35 | 2004-11-13 | Delta | Alexander | Yes | ....
To have it look like:
+-------------+-----------+-------+-------------+---------+ | LastName | FirstName | AttID | SessionDate | Present | +-------------+-----------+-------+-------------+---------+ | 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 | | | | 12 | 2004-11-13 | Yes | | Beta | Elaine | 3 | 2004-10-30 | Yes | | | | 13 | 2004-11-06 | Yes | | Delta | Alexander | 14 | 2004-11-06 | Yes | | | | 35 | 2004-11-13 | Yes | .....
Please advise I am running on mySql 4.0
I usually do this in my application code, by only printing values in the name columns when they change, but something like the following should work:
SET @ln = '', @fn = '';
SELECT
IF(P.LastName = @ln, '', @ln:= P.LastName) AS 'LastName',
IF(P.FirstName = @fn, '', @fn:= P.FirstName) AS 'FirstName',
A.AttID,
S.SessionDate,
A.Present
FROM Attendance A, Sessions S, Participants P
WHERE S.SessionID = A.Session AND P.Part_ID = A.Participant
ORDER BY P.LastName, P.FirstName, S.SessionDate;Hmmm.. Now that I think about it, that won't print the last name if only the first name changes ('Smith, Jane' followed by 'Smith, John'), nor will it print the first name if only the last name changes ('Johnson, Amy' followed by 'Jones, Amy'). Perhaps this would be better:
SET @name = '';
SELECT
IF(CONCAT(P.LastName, ', ', P.FirstName) = @name,
'',
@name:= CONCAT(P.LastName, ', ', P.FirstName)
) AS 'Name',
A.AttID,
S.SessionDate,
A.Present
FROM Attendance A, Sessions S, Participants P
WHERE S.SessionID = A.Session AND P.Part_ID = A.Participant
ORDER BY P.LastName, P.FirstName, S.SessionDate;Michael
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
