Re: this sql is returning duplicates

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')

--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.