SEO

#1 (**permalink**) 03-17-2008, 06:13 AM

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

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

how: many-to-many with LEFT JOIN