--- In firebird-support@yahoogroups.com, "d_dude_2003"
<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?

Well which MIC.COMMENTLINE do you want in that case?

SQL is a very precise language. You get what you ask for. You ask it
to join to MOC_ITEMCOMMENTS which is a 1..* join.

You probably want a group by on every other field, and some aggregate
(eg min(MIC.COMMENTLINE)).

Adam