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/[EMAIL PROTECTED]
