
04/09/2006, 17:05
|
 | | | Fecha de Ingreso: agosto-2002
Mensajes: 688
Antigüedad: 22 años, 7 meses Puntos: 0 | |
Error con 5 LEFT JOINs Hola
Mi problema es el sig:
tengo la siguiente consulta (codigo 1) y funciona excelentemente bien, pero cuando agrego un left join mas .... simplemente se traba (codigo 2)
codigo 1
Código:
SELECT concat( ux.user2, '-', ux.user6 ) AS groupkey, ux.user12 AS ibo_level_sort, ux.user12 AS ibo_level, concat( ux.user2, '-', ux.user6 ) AS placement, concat( u.first_name, ' ', u.last_name ) AS name, date_format( activity_start_dt, "%m/%d/%Y" ) AS step, sum(
IF (
vp.leftright_ind = 'L', vp.personal_volume, 0
) ) AS PriorLPV, sum(
IF (
vp.leftright_ind = 'R', vp.personal_volume, 0
) ) AS PriorRPV, sum(
IF (
vc.leftright_ind = 'L', vc.personal_volume, 0
) ) AS CurrentLPV, sum(
IF (
vc.leftright_ind = 'R', vc.personal_volume, 0
) ) AS CurrentRPV, sum(
IF (
vc.leftright_ind = 'L', vc.group_volume, 0
) ) AS LBV, sum(
IF (
vc.leftright_ind = 'R', vc.group_volume, 0
) ) AS RBV
FROM mambophil_user_extended AS ux
LEFT JOIN mambophil_users AS u ON u.id = ux.user_id
LEFT JOIN mambophil_adv_users AS au ON au.id = ux.user_id
LEFT JOIN mambophil_pshop_order_volume AS vp ON vp.ibo_id = ux.user_id AND FROM_UNIXTIME( vp.order_date ) >= '2006-08-01' AND FROM_UNIXTIME( vp.order_date ) <= '2006-08-31'
LEFT JOIN mambophil_pshop_order_volume AS vc ON vc.ibo_id = ux.user_id AND FROM_UNIXTIME( vc.order_date ) >= '2006-09-01' AND FROM_UNIXTIME( vc.order_date ) <= '2006-09-30'
WHERE 1 = 1 AND ux.user12 >= 19 AND ux.user11 >= 20 AND ux.user11 < 37577
GROUP BY 1
ORDER BY user11
LIMIT 0 , 2
codigo 2
Código:
SELECT
concat( ux.user2, '-', ux.user6 ) AS groupkey, ux.user12 AS ibo_level_sort ,
ux.user12 AS ibo_level , concat( ux.user2, '-', ux.user6 ) AS placement ,
concat(u.first_name,' ',u.last_name) as name ,
date_format( activity_start_dt, "%m/%d/%Y" ) AS step ,
sum(IF (vp.leftright_ind = 'L', vp.personal_volume, 0) ) AS PriorLPV ,
sum(IF (vp.leftright_ind = 'R', vp.personal_volume, 0) ) AS PriorRPV ,
sum(IF (vc.leftright_ind = 'L', vc.personal_volume, 0) ) AS CurrentLPV ,
sum(IF (vc.leftright_ind = 'R', vc.personal_volume, 0) ) AS CurrentRPV ,
sum(IF (vc.leftright_ind = 'L', vc.group_volume, 0) ) AS LBV ,
sum(IF (vc.leftright_ind = 'R', vc.group_volume, 0) ) AS RBV , rh.award_ctry_pin
FROM
mambophil_user_extended AS ux
LEFT JOIN mambophil_users AS u ON u.id = ux.user_id
LEFT JOIN mambophil_adv_users AS au ON au.id = ux.user_id
LEFT JOIN mambophil_rank_history AS rh ON ux.user1 = rh.user_id
LEFT JOIN mambophil_pshop_order_volume AS vp ON vp.ibo_id = ux.user_id
AND
FROM_UNIXTIME( vp.order_date ) >= '2006-08-01'
AND
FROM_UNIXTIME( vp.order_date ) <= '2006-08-31'
LEFT JOIN mambophil_pshop_order_volume AS vc ON vc.ibo_id = ux.user_id
AND
FROM_UNIXTIME( vc.order_date ) >= '2006-09-01' and FROM_UNIXTIME( vc.order_date ) <= '2006-09-30'
WHERE 1 = 1 and ux.user12 >= 19 and ux.user11 >= 20 and ux.user11 < 37577
GROUP BY 1 ORDER BY user11
LIMIT 0 , 2
En donde estoy mal?
Solo estoy modificando un par de lineas (pero mal  )
Alguien me podria ayudar?
GRacias !! |