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 ***.*,dcv.*,ana.*,ar.*,c.*,
ar.descripcion as descripcionArticulo ,
dcv.valorVenta as valorVentaDetalle,
m.nombre as nombreMedida
FROM cabeceracomprobanteventa ***
LEFT JOIN detallecomprobanteventa dcv ON dcv.idCabeceraComprobanteVenta=***.idCabeceraComprobanteVenta
LEFT JOIN articulonoauto ana ON ***.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=***.idCliente
WHERE ***.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 ***.*,dcv.*,ana.*,ar.*,c.*,
ar.descripcion as descripcionArticulo ,
dcv.valorVenta as valorVentaDetalle,
m.nombre as nombreMedida
FROM articulonoautocabeceracomprobanteventa link
LEFT JOIN cabeceracomprobanteventa *** ON ***.idCabeceraComprobanteVenta=link.idCabeceraComprobanteVenta
LEFT JOIN articulonoauto ana ON link.idArticuloNoAuto=ana.idArticuloNoAuto
LEFT JOIN detallecomprobanteventa dcv ON dcv.idCabeceraComprobanteVenta=***.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=***.idCliente
WHERE ***.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
dBforums
>
how: many-to-many with LEFT JOIN
03-10-08, 23:17
Linear Mode
