how: many-to-many with LEFT JOIN

[dr_pompeii <dr_pompeii@xxxxxxxxx>]

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 [b]sell/contain one[/b] 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'
[/code]

i can see in the IReport preview 
the header of the Sales Receipt and the MotorCycle and maybe 
[b](not always exists some items "detallecomprobanteventa", like oil,car
parts)[/b]


the last new requeriment was that a wonderful
1 CabeceraComprobanteVenta can sell/contain [b]many items[/b] 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
[b]FROM articulonoautocabeceracomprobanteventa link[/b]
LEFT JOIN cabeceracomprobanteventa ccv ON
[b]ccv.idCabeceraComprobanteVenta=link.idCabeceraComprobanteVenta[/b]
LEFT JOIN articulonoauto ana ON
[b]link.idArticuloNoAuto=ana.idArticuloNoAuto[/b]
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'
[/code]

see now this [b]FROM articulonoautocabeceracomprobanteventa link[/b]

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 [b]without 2[/b] 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


-- 
View this message in context: http://www.nabble.com/how%3A-many-to-many-with-LEFT-JOIN-tp15974071p15974071.html
Sent from the MySQL - General mailing list archive at Nabble.com.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=royale@xxxxxxxxxx