Hello guys

before to a last doom requeriment,
i used to work with
this type of relation
ArticuloNoAuto (english ArticleNoCar like MotorCycle or Motor)
and CabeceraComprobanteVenta (like a header of Receipt of some sell "SalesReceiptHeader")

so 1 CabeceraComprobanteVenta can sell/contain one ArticuloNoAuto
and 1 ArticuloNoAuto can be contained in 1 to 2 CabeceraComprobanteVenta (normal)
or (canceled,newnormal)

so this sql work fine
Code:
SELECT ccv.*,dcv.*,ana.*,ar.*,c.*,
ar.descripcion as descripcionArticulo , 
dcv.valorVenta as valorVentaDetalle,
m.nombre as nombreMedida
FROM cabeceracomprobanteventa ccv
LEFT JOIN detallecomprobanteventa dcv ON dcv.idCabeceraComprobanteVenta=ccv.idCabeceraComprobanteVenta
LEFT JOIN articulonoauto ana ON ccv.idArticuloNoAuto=ana.idArticuloNoAuto
LEFT JOIN articulo ar ON dcv.idArticulo=ar.idArticulo
LEFT JOIN medida m ON m.idMedida=ar.idMedida
LEFT JOIN cliente c ON c.idCliente=ccv.idCliente
WHERE ccv.numComprobanteVenta='003-000003'
i can see in the IReport preview
the header of the Sales Receipt and the MotorCycle and maybe
(not always exists some items "detallecomprobanteventa", like oil,car parts)


the last new requeriment was that a wonderful
1 CabeceraComprobanteVenta can sell/contain many items ArticuloNoAuto (1-4)
and 1 ArticuloNoAuto can be contained in 1 to 2 CabeceraComprobanteVenta (normal)
or (canceled,newnormal)

so we have a relation many-to-many already resolved by hibernate with a link table
called articulonoautocabeceracomprobanteventa

so my new sql query is
Code:
SELECT ccv.*,dcv.*,ana.*,ar.*,c.*,
ar.descripcion as descripcionArticulo , 
dcv.valorVenta as valorVentaDetalle,
m.nombre as nombreMedida
FROM articulonoautocabeceracomprobanteventa link
LEFT JOIN cabeceracomprobanteventa ccv ON ccv.idCabeceraComprobanteVenta=link.idCabeceraComprobanteVenta
LEFT JOIN articulonoauto ana ON link.idArticuloNoAuto=ana.idArticuloNoAuto
LEFT JOIN detallecomprobanteventa dcv ON dcv.idCabeceraComprobanteVenta=ccv.idCabeceraComprobanteVenta
LEFT JOIN articulo ar ON dcv.idArticulo=ar.idArticulo
LEFT JOIN medida m ON m.idMedida=ar.idMedida
LEFT JOIN cliente c ON c.idCliente=ccv.idCliente
WHERE ccv.numComprobanteVenta='003-888888'
see now this FROM articulonoautocabeceracomprobanteventa link

the code works but there is a problematic detail

i have this situation,
if i want to sell 2 MotorCycle with 2 car parts

in my IReport preview i see 4 MotorCycles (the 2 desired and theses same repeated 2 times)

why?

if i sell 2 MotorCycle without 2 car parts i can see the only 2 desired MotorCycle

whay is wrong???
and how i can resolve this??
some missing condition in the query?

thanks in advanced

p.d: mysql 5.0.27