MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » January 2014

Re: this sql is returning duplicates



fixed

On 22-Jan-2014 15:27 -0800, Hoteltravelfundotcom wrote:
If I just query on the main table OEINDLID, I will get only half the
rows. <<SNIP>> how can I resolve this? <<SNIP>>

According to the feedback in a followup reply that suggested the duplicates come from the third joined table-reference, the following query that requests a DISTINCT derived-table result across the referenced columns of the third file, should suffice to avoid those duplicates. Not to imply however, that the effect would be the desired\correct output; e.g. there may be some specific grouping, possibly with a MIN or MAX value of a particular column that should ensure the results of the data selected from the third table are distinct.

SELECT
T01.IDORD#, T01.IDDOCD, T01.IDPRT#, T01.IDSHP#, T01.IDNTU$
, ( T01.IDNTU$ * T01.IDSHP# ) AS LINTOT
, T02.IAPTWT, T02.IARCC3
, T03.MHSTAT, T03.MHZONR, T03.MHSHIN
, T01.IDORDT, T01.IDHCD3
FROM ASTDTA.OEINDLID T01
INNER JOIN ASTDTA.ICPRTMIA T02
ON T01.IDPRT# = T02.IAPRT#
INNER JOIN (SELECT DISTINCT
MHORDP, MHSTAT, MHZONR, MHSHIN
FROM ASTDTA.MFHHMHPK
) AS T03
ON T01.IDORD# = T03.MHORDP
WHERE T01.IDHCD3 in ('MDL','TRP')






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact