CODEWITHSUNDEEP
mysql
PipRon7
PipRon7

Reputation: 87

mysql distinct or grouping query

I have the below query:-

Select
  PUB.oa_inthed.adcode As Address_Code,
  PUB.oa_intnom."due-date" As Commitment_Due_Date,
  PUB.oa_inthed.company As Company,
  PUB.oa_intnom.costcentre As Cost_Code,
  PUB.oa_inthed.currency As Currency,
  PUB.oa_inthed.description As Description,
  PUB.oa_inthed.docdate As Document_Date,
  PUB.oa_inthed."doc-id" As Document_Type,
  PUB.oa_intnom.expensecode As Expense_Code,
  PUB.oa_inthed.intid As Interface_Record_Key,
  PUB.oa_inthed.posted As "Posted?",
  PUB.oa_inthed.srcaccount As Source_Account,
  PUB.oa_inthed.docval As Value_Cur,
  PUB.oa_inthed.period As Period,
  PUB.oa_inthed.stat As Status
From
  PUB.oa_inthed Inner Join
  PUB.oa_intnom On PUB.oa_intnom.intid = PUB.oa_inthed.intid Inner Join
  PUB.oa_intextra On PUB.oa_intextra.intid = PUB.oa_intnom.intid
Where
  PUB.oa_inthed.company = 01 And
  PUB.oa_inthed.intid = 'XM618197%' And
  PUB.oa_intextra.rectype = 'REQHEAD'

and it returns the below data:-

enter image description here

How can I get a distinct line for each intid?

I am using MySQL.

All advice welcome

Upvotes: 0

Views: 31

Answers (1)

Kostas Mitsarakis
Kostas Mitsarakis

Reputation: 4747

Try this (untested):

SELECT PUB.oa_inthed.intid, 
    PUB.oa_inthed.adcode As Address_Code, 
    PUB.oa_intnom."due-date" As Commitment_Due_Date,
    PUB.oa_inthed.company As Company,
    PUB.oa_intnom.costcentre As Cost_Code,
    PUB.oa_inthed.currency As Currency,
    PUB.oa_inthed.description As Description,
    PUB.oa_inthed.docdate As Document_Date,
    PUB.oa_inthed."doc-id" As Document_Type,
    PUB.oa_intnom.expensecode As Expense_Code,
    PUB.oa_inthed.posted As "Posted?",
    PUB.oa_inthed.srcaccount As Source_Account,
    PUB.oa_inthed.docval As Value_Cur,
    PUB.oa_inthed.period As Period,
    PUB.oa_inthed.stat As Status
FROM PUB.oa_inthed
INNER JOIN PUB.oa_intnom 
ON PUB.oa_intnom.intid = PUB.oa_inthed.intid 
INNER JOIN PUB.oa_intextra 
ON PUB.oa_intextra.intid = PUB.oa_intnom.intid
INNER JOIN (
    SELECT PUB.oa_inthed.intid
    FROM PUB.oa_inthed
    GROUP BY PUB.oa_inthed.intid
) AS _aa
ON PUB.oa_inthed.intid = _aa.PUB.oa_inthed.intid
WHERE PUB.oa_inthed.company = 01 AND PUB.oa_inthed.intid = 'XM618197%' AND PUB.oa_intextra.rectype = 'REQHEAD'

Upvotes: 1

Related Questions