RE: [PHP] trouble with mysql query

Try: SELECT supportmain_pk,supportmain_originator, staff_pk, supportaudit_id,supportaudit_date, supportaudit_type FROM supportmain, staff, supportaudit WHERE supportmain_originator=staff_pk and supportmain_pk=supportaudit_id and ((supportaudit_type > 0 and supportaudit_type < 5) or (supportaudit_type >= 5 and (supportaudit_date > 20011014 and supportaudit_date <= 20011021))) GROUP BY supportmain_pk -----Original Message----- From: Adam Plocher [mailto:aplocher@erepublic.com] Sent: Wednesday, October 17, 2001 2:08 PM To: 'php-general@lists.php.net' Subject: [PHP] trouble with mysql query I know this isn't exactly PHP, but it is PHP related. I have this MySQL query that I cannot get to work the way I want it to. It looks like this: SELECT supportmain_pk,supportmain_originator, staff_pk, supportaudit_id,supportaudit_date, supportaudit_type FROM supportmain, staff, supportaudit WHERE supportmain_originator=staff_pk and supportmain_pk=supportaudit_id and ((supportaudit_type > 0 and supportaudit_type < 5) or (supportaudit_type >= 5 and (supportaudit_date > 20011014 and supportaudit_date <= 20011021))) The way this works is, there is a table called supportmain with a number of unique records in it. Each record in the supportmain table has multiple records associated to it, through the supportaudit table (associated by primary key). The supportaudit table holds the status of the data in the supportmain (thats what the supportaudit_type record is, specifically), and the status is measured with an integer. The part that I'm really having problems with is the WHERE clause in this query. I need this to get the largest number from the supportaudit_type field where the supportmain_pk=supportaudit_id (supportaudit_id is the field that associates supportaudit to supportmain, supportmain_pk is a unique column that supportaudit_id uses), instead of every record from the supportaudit table... Each supportmain record should only return one thing, but I find that it is returning a record for every record in the supportaudit table. Is there anyway I can do this with this one single query? This was kind of hard to explain, so if my problem isn't clear, I'm sorry. Any help would be very much appreciated. Thanks -Adam