Double Data record in Column

Question

HI, I had problem when join the table it will show the double data record in the column. kindly advise, thank you. 

SELECT A.INV_TRANS_ID
      ,A.INV_ID
      ,B.INV_TYPE
      ,B.INV_SHORTDESC
      ,A.INV_TRANS_LOCATION
      ,A.INV_TRANS_QTY
      ,A.INV_TRANS_REQUESTOR
      ,A.INV_TRANS_SPEC
      ,A.INV_TRANS_REFNO
      ,A.INV_TRANS_REMARK
      ,A.INV_REASON_ID
      ,A.INV_REASON_REMARK
      ,A.INV_CREATE_DATE
      ,CONVERT(CHAR(8), A.INV_CREATE_DATE, 114) AS INV_CREATE_TIME
      ,A.INV_CREATE_USER + ' ' + C.INV_EMP_NAME AS INV_CREATE_USER
      ,A.INV_VENDORS
      ,A.INV_FROMLOC
  FROM OTH_INV_TRANSACTION A
  JOIN OTH_INV_DETAILS B ON A.INV_ID=B.INV_ID
  JOIN OTH_INV_USER C ON C.INV_EMP_ID = A.INV_CREATE_USER
  JOIN OTH_INV_REASON D ON A.INV_REASON_ID = D.INV_REASON_ID
  WHERE 1=1

ORDER BY A.INV_CREATE_DATE, B.INV_TYPE,A.INV_ID

Answer 1

In your first statement, you are directly selecting from 1 table for a particular value combination. In your second statement you are joining between multiple tables. If the tables you are joining have a 1-Many relationship, SQL will return you the rows which all match the join condition. To eliminate such duplicates, please try using SELECT DISTINCT. Example below

SELECT DISTINCT
       A.INV_TRANS_ID
      ,A.INV_ID
      ,B.INV_TYPE
      ,B.INV_SHORTDESC
      ,A.INV_TRANS_LOCATION
      ,A.INV_TRANS_QTY
      ,A.INV_TRANS_REQUESTOR
      ,A.INV_TRANS_SPEC
      ,A.INV_TRANS_REFNO
      ,A.INV_TRANS_REMARK
      ,A.INV_REASON_ID
      ,A.INV_REASON_REMARK
      ,A.INV_CREATE_DATE
      ,CONVERT(CHAR(8), A.INV_CREATE_DATE, 114) AS INV_CREATE_TIME
      ,A.INV_CREATE_USER + ' ' + C.INV_EMP_NAME AS INV_CREATE_USER
      ,A.INV_VENDORS
      ,A.INV_FROMLOC
  FROM OTH_INV_TRANSACTION A
  JOIN OTH_INV_DETAILS B ON A.INV_ID=B.INV_ID
  JOIN OTH_INV_USER C ON C.INV_EMP_ID = A.INV_CREATE_USER
  JOIN OTH_INV_REASON D ON A.INV_REASON_ID = D.INV_REASON_ID
  WHERE 1=1

ORDER BY A.INV_CREATE_DATE, B.INV_TYPE,A.INV_ID

Answer 2

Try

SELECT distinct A.INV_TRANS_ID
      ,A.INV_ID
      ,B.INV_TYPE
      ,B.INV_SHORTDESC
      ,A.INV_TRANS_LOCATION
      ,A.INV_TRANS_QTY
      ,A.INV_TRANS_REQUESTOR
      ,A.INV_TRANS_SPEC
      ,A.INV_TRANS_REFNO
      ,A.INV_TRANS_REMARK
      ,A.INV_REASON_ID
      ,A.INV_REASON_REMARK
      ,A.INV_CREATE_DATE
      ,CONVERT(CHAR(8), A.INV_CREATE_DATE, 114) AS INV_CREATE_TIME
      ,A.INV_CREATE_USER + ' ' + C.INV_EMP_NAME AS INV_CREATE_USER
      ,A.INV_VENDORS
      ,A.INV_FROMLOC
  FROM OTH_INV_TRANSACTION A
  JOIN OTH_INV_DETAILS B ON A.INV_ID=B.INV_ID
  JOIN OTH_INV_USER C ON C.INV_EMP_ID = A.INV_CREATE_USER
  JOIN OTH_INV_REASON D ON A.INV_REASON_ID = D.INV_REASON_ID
  WHERE 1=1

ORDER BY A.INV_CREATE_DATE, B.INV_TYPE,A.INV_ID

---

Many Thanks & Best Regards, Hua Min

Answer 3

In your result Double data is qualified due to the records persent in the table.

To avoide this record you can use DISTINCT in your select statement or GROUP BY in your select statement where condition  

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 4

these are not duplicate for sql but for you, until you give some more condition or group them, select max, top whatever.

i can see there are different '.INV_TRANS_QTY' entry for your query so you need to update query on your logic or describe us to help.

Answer 5

As is the case here, many will advise you to add DISTINCT, but in my opinion that is usually a clutch. The fact that you are getting duplicate rows is usually an indicator that your query is not correct.

In this case, you select from 4 tables (aliased as A, B, C, D), but only actually use data from 3 of them. This begs the question: do you need the join with OTH_INV_REASON? If not, then just eliminate the JOIN with it. If you do need it, then rewrite it to an EXISTS clause. See pseudo code below:

... JOIN OTH_INV_USER C
  ON C.INV_EMP_ID = A.INV_CREATE_USER
WHERE EXISTS (
  SELECT *
  FROM OTH_INV_REASON
  WHERE D.INV_REASON_ID = A.INV_REASON_ID
)

---

Gert-Jan