Simply eliminating those rows you're not interested in in your JOIN clause sounds the simplest answer. E.g.

LEFT JOIN MOC_ITEMCOMMENTS MIC ON MIC.MOCITEMID = MI.MOCITEMID and not exists(
select * from MOC_ITEMCOMMENTS MIC2
where MIC2.MOCITEMID = MI.MOCITEMID
AND MIC2.PRIMARY_KEY_FIELD < MIC.PRIMARY_KEY_FIELD)

will eliminate all but the COMMENTLINE with the lowest PRIMARY_KEY_FIELD for that item.

HTH,
Set

d_dude_2003 wrote:

> Here is the query
>
> SELECT MI.*,
> PS.NAME AS SENDERPARTY,
> PE.NAME AS SENDERPERSON,
> PR.NAME AS RECIPIENTPARTY,
> PEMO.NAME AS RECIPIENTPERSON,
> MC.CATEGORY,
> MSC.SUBCATEGORY,
> SS.NAME AS STATUS,
> PP.NAME AS PRIORITY,
> SA.NAME AS ACCESSSTATUS,
> MIC.COMMENTLINE,
> MCI.USERID AS COPIEDUSERID
> FROM MOC_ITEMS MI
> JOIN PARTIES PS ON PS.PARTYID = MI.SENDERPARTYID
> JOIN PARTIES PR ON PR.PARTYID = MI.RECIPIENTPARTYID
> JOIN PARTYEMPLOYEES PE ON PE.OFFICEEMPLOYEEID = MI.SENDERPERSONID
> JOIN PARTYEMPLOYEES PEMO ON PEMO.OFFICEEMPLOYEEID =
> MI.RECIPIENTPERSONID
> JOIN STATUSES SA ON SA.STATUSID = MI.ACCESSSTATUSID
> JOIN MOC_CATEGORIES MC ON MC.MOCCATEGORYID = MI.MOCCATEGORYID
> JOIN MOC_SUBCATEGORIES MSC ON MSC.MOCSUBCATEGORYID =
> MI.MOCSUBCATEGORYID
> JOIN STATUSES SS ON SS.STATUSID = MI.STATUSID
> JOIN PRIORITIES PP ON PP.PRIORITYID = MI.PRIORITYID
> LEFT JOIN MOC_ITEMCOMMENTS MIC ON MIC.MOCITEMID = MI.MOCITEMID
> LEFT JOIN MOC_COPIEDITEMS MCI ON MCI.ID = MI.MOCITEMID
> WHERE (MI.MOCITEMID = 25259)
>
> If there are several rows in MOC_ITEMCOMMENTS, the query returns all
> of them with the same MOCITEMID.
>
> How do i change the query so it returns only single row regardless
> of count of comments?
>
> Thanks.

[Non-text portions of this message have been removed]