subquery support?
subquery support?
I get error with this query:
SELECT entradas.matricula, entradas.modelo, mov_venta.cliente, mov_venta.f_reserva, mov_venta.f_confirmacion, mov_taller.f_prevision,
mov_taller.f_reparado, talleres.taller, status_peritaje.importe,
(SELECT COUNT(repair_incidencias.incidencia_id) AS counter
FROM repair_incidencias
WHERE repair_incidencias.matricula = mov_venta.matricula AND repair_incidencias.completado = 'False') AS desperfectos
FROM entradas RIGHT OUTER JOIN
mov_taller INNER JOIN
talleres ON mov_taller.taller_id = talleres.taller_id RIGHT OUTER JOIN
mov_salidas RIGHT OUTER JOIN
mov_venta ON mov_salidas.matricula = mov_venta.matricula LEFT OUTER JOIN
status_peritaje ON mov_venta.matricula = status_peritaje.matricula ON mov_taller.matricula = mov_venta.matricula ON
entradas.matricula = mov_venta.matricula
WHERE (mov_salidas.matricula IS NULL) AND (mov_venta.f_cancelado IS NULL) AND (mov_venta.profesional = 'True') AND (entradas.campa_id = 1)
GROUP BY mov_venta.venta_id
SELECT entradas.matricula, entradas.modelo, mov_venta.cliente, mov_venta.f_reserva, mov_venta.f_confirmacion, mov_taller.f_prevision,
mov_taller.f_reparado, talleres.taller, status_peritaje.importe,
(SELECT COUNT(repair_incidencias.incidencia_id) AS counter
FROM repair_incidencias
WHERE repair_incidencias.matricula = mov_venta.matricula AND repair_incidencias.completado = 'False') AS desperfectos
FROM entradas RIGHT OUTER JOIN
mov_taller INNER JOIN
talleres ON mov_taller.taller_id = talleres.taller_id RIGHT OUTER JOIN
mov_salidas RIGHT OUTER JOIN
mov_venta ON mov_salidas.matricula = mov_venta.matricula LEFT OUTER JOIN
status_peritaje ON mov_venta.matricula = status_peritaje.matricula ON mov_taller.matricula = mov_venta.matricula ON
entradas.matricula = mov_venta.matricula
WHERE (mov_salidas.matricula IS NULL) AND (mov_venta.f_cancelado IS NULL) AND (mov_venta.profesional = 'True') AND (entradas.campa_id = 1)
GROUP BY mov_venta.venta_id
Last edited by dooh on Tue 10 Jul 2007 15:01, edited 2 times in total.
CREATE TABLE `entradas` (
`matricula` varchar(10) NOT NULL,
`completada` varchar(5) default 'false',
`f_entrada` datetime default NULL,
`f_updated` timestamp NULL default NULL on update CURRENT_TIMESTAMP,
PRIMARY KEY (`matricula`),
UNIQUE KEY `matricual_key` (`matricula`),
KEY `fbk_campa_id` (`campa_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `mov_taller` (
`movimiento_id` int(7) NOT NULL auto_increment,
`matricula` varchar(10) NOT NULL default '',
`taller_id` int(7) NOT NULL,
`f_asignacion` datetime default NULL,
`f_prevision` datetime default NULL,
`f_entrada` datetime default NULL,
`f_reparado` datetime default NULL,
`f_entrega` datetime default NULL,
`f_updated` timestamp NULL default NULL on update CURRENT_TIMESTAMP,
PRIMARY KEY (`movimiento_id`),
KEY `matricula` (`matricula`),
KEY `mov_taller_ibfk_2` (`taller_id`),
KEY `mov_taller_ibfk_3` (`userid`),
CONSTRAINT `mov_taller_ibfk_1` FOREIGN KEY (`matricula`) REFERENCES `entradas` (`matricula`) ON UPDATE CASCADE,
CONSTRAINT `mov_taller_ibfk_2` FOREIGN KEY (`taller_id`) REFERENCES `talleres` (`taller_id`) ON UPDATE CASCADE,
CONSTRAINT `mov_taller_ibfk_3` FOREIGN KEY (`userid`) REFERENCES `aspnet_users` (`userid`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `repair_incidencias` (
`incidencia_id` int(7) NOT NULL auto_increment,
`accion_id` int(7) NOT NULL,
`desperfecto_id` int(7) NOT NULL,
`localizacion_id` int(7) NOT NULL,
`observaciones` varchar(255) default NULL,
`matricula` varchar(10) NOT NULL,
`importe` double(7,2) NOT NULL default '0.00',
`recambio` varchar(5) default 'false',
`completado` varchar(5) NOT NULL default 'false',
`userid` varchar(40) NOT NULL,
`f_updated` timestamp NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
PRIMARY KEY (`incidencia_id`),
KEY `matricula` (`matricula`),
KEY `userid` (`userid`),
CONSTRAINT `repair_incidencias_ibfk_3` FOREIGN KEY (`matricula`) REFERENCES `entradas` (`matricula`) ON UPDATE CASCADE,
CONSTRAINT `repair_incidencias_ibfk_4` FOREIGN KEY (`userid`) REFERENCES `aspnet_users` (`userid`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SELECT entradas.campa_id, entradas.f_entrada, entradas.matricula, mov_taller.f_entrega,
(SELECT COUNT(repair_incidencias.incidencia_id)
FROM repair_incidencias
WHERE repair_incidencias.matricula = entradas.matricula) AS desperfectos
FROM entradas LEFT OUTER JOIN
mov_taller ON entradas.matricula = mov_taller.matricula
If i run this query directly in to any MySQL Query, i get no errors and i get the correct result, but if i run this query into a tableadapter with corelab, i get the follow error:
Error in SELECT clause: expression near 'SELECT'.
Error in SELECT clause: expression near 'FROM'.
Missing FROM clause.
Unable to parse query text.
`matricula` varchar(10) NOT NULL,
`completada` varchar(5) default 'false',
`f_entrada` datetime default NULL,
`f_updated` timestamp NULL default NULL on update CURRENT_TIMESTAMP,
PRIMARY KEY (`matricula`),
UNIQUE KEY `matricual_key` (`matricula`),
KEY `fbk_campa_id` (`campa_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `mov_taller` (
`movimiento_id` int(7) NOT NULL auto_increment,
`matricula` varchar(10) NOT NULL default '',
`taller_id` int(7) NOT NULL,
`f_asignacion` datetime default NULL,
`f_prevision` datetime default NULL,
`f_entrada` datetime default NULL,
`f_reparado` datetime default NULL,
`f_entrega` datetime default NULL,
`f_updated` timestamp NULL default NULL on update CURRENT_TIMESTAMP,
PRIMARY KEY (`movimiento_id`),
KEY `matricula` (`matricula`),
KEY `mov_taller_ibfk_2` (`taller_id`),
KEY `mov_taller_ibfk_3` (`userid`),
CONSTRAINT `mov_taller_ibfk_1` FOREIGN KEY (`matricula`) REFERENCES `entradas` (`matricula`) ON UPDATE CASCADE,
CONSTRAINT `mov_taller_ibfk_2` FOREIGN KEY (`taller_id`) REFERENCES `talleres` (`taller_id`) ON UPDATE CASCADE,
CONSTRAINT `mov_taller_ibfk_3` FOREIGN KEY (`userid`) REFERENCES `aspnet_users` (`userid`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `repair_incidencias` (
`incidencia_id` int(7) NOT NULL auto_increment,
`accion_id` int(7) NOT NULL,
`desperfecto_id` int(7) NOT NULL,
`localizacion_id` int(7) NOT NULL,
`observaciones` varchar(255) default NULL,
`matricula` varchar(10) NOT NULL,
`importe` double(7,2) NOT NULL default '0.00',
`recambio` varchar(5) default 'false',
`completado` varchar(5) NOT NULL default 'false',
`userid` varchar(40) NOT NULL,
`f_updated` timestamp NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
PRIMARY KEY (`incidencia_id`),
KEY `matricula` (`matricula`),
KEY `userid` (`userid`),
CONSTRAINT `repair_incidencias_ibfk_3` FOREIGN KEY (`matricula`) REFERENCES `entradas` (`matricula`) ON UPDATE CASCADE,
CONSTRAINT `repair_incidencias_ibfk_4` FOREIGN KEY (`userid`) REFERENCES `aspnet_users` (`userid`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SELECT entradas.campa_id, entradas.f_entrada, entradas.matricula, mov_taller.f_entrega,
(SELECT COUNT(repair_incidencias.incidencia_id)
FROM repair_incidencias
WHERE repair_incidencias.matricula = entradas.matricula) AS desperfectos
FROM entradas LEFT OUTER JOIN
mov_taller ON entradas.matricula = mov_taller.matricula
If i run this query directly in to any MySQL Query, i get no errors and i get the correct result, but if i run this query into a tableadapter with corelab, i get the follow error:
Error in SELECT clause: expression near 'SELECT'.
Error in SELECT clause: expression near 'FROM'.
Missing FROM clause.
Unable to parse query text.
The same without the reference keys.
CREATE TABLE `entradas` (
`matricula` varchar(10) NOT NULL,
`completada` varchar(5) default 'false',
`f_entrada` datetime default NULL,
`f_updated` timestamp NULL default NULL on update CURRENT_TIMESTAMP,
PRIMARY KEY (`matricula`),
UNIQUE KEY `matricual_key` (`matricula`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `mov_taller` (
`movimiento_id` int(7) NOT NULL auto_increment,
`matricula` varchar(10) NOT NULL default '',
`taller_id` int(7) NOT NULL,
`f_asignacion` datetime default NULL,
`f_prevision` datetime default NULL,
`f_entrada` datetime default NULL,
`f_reparado` datetime default NULL,
`f_entrega` datetime default NULL,
`f_updated` timestamp NULL default NULL on update CURRENT_TIMESTAMP,
PRIMARY KEY (`movimiento_id`),
KEY `matricula` (`matricula`),
KEY `mov_taller_ibfk_2` (`taller_id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `repair_incidencias` (
`incidencia_id` int(7) NOT NULL auto_increment,
`accion_id` int(7) NOT NULL,
`desperfecto_id` int(7) NOT NULL,
`localizacion_id` int(7) NOT NULL,
`observaciones` varchar(255) default NULL,
`matricula` varchar(10) NOT NULL,
`importe` double(7,2) NOT NULL default '0.00',
`recambio` varchar(5) default 'false',
`completado` varchar(5) NOT NULL default 'false',
`userid` varchar(40) NOT NULL,
`f_updated` timestamp NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
PRIMARY KEY (`incidencia_id`),
KEY `matricula` (`matricula`),
KEY `userid` (`userid`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `entradas` (
`matricula` varchar(10) NOT NULL,
`completada` varchar(5) default 'false',
`f_entrada` datetime default NULL,
`f_updated` timestamp NULL default NULL on update CURRENT_TIMESTAMP,
PRIMARY KEY (`matricula`),
UNIQUE KEY `matricual_key` (`matricula`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `mov_taller` (
`movimiento_id` int(7) NOT NULL auto_increment,
`matricula` varchar(10) NOT NULL default '',
`taller_id` int(7) NOT NULL,
`f_asignacion` datetime default NULL,
`f_prevision` datetime default NULL,
`f_entrada` datetime default NULL,
`f_reparado` datetime default NULL,
`f_entrega` datetime default NULL,
`f_updated` timestamp NULL default NULL on update CURRENT_TIMESTAMP,
PRIMARY KEY (`movimiento_id`),
KEY `matricula` (`matricula`),
KEY `mov_taller_ibfk_2` (`taller_id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `repair_incidencias` (
`incidencia_id` int(7) NOT NULL auto_increment,
`accion_id` int(7) NOT NULL,
`desperfecto_id` int(7) NOT NULL,
`localizacion_id` int(7) NOT NULL,
`observaciones` varchar(255) default NULL,
`matricula` varchar(10) NOT NULL,
`importe` double(7,2) NOT NULL default '0.00',
`recambio` varchar(5) default 'false',
`completado` varchar(5) NOT NULL default 'false',
`userid` varchar(40) NOT NULL,
`f_updated` timestamp NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
PRIMARY KEY (`incidencia_id`),
KEY `matricula` (`matricula`),
KEY `userid` (`userid`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;