This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

nkoriginal
nkoriginal used Ask the Experts™
on
Hello, when i tried to run my query I get this error
 This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
im using mysql database.
I've 2 tables noticias(news) and imagenes(images)
I've 1 multiples images for 1 news, but I need to return just 1 news and 1 images. That's the reason I tried to limite the subquery.
can anyone help me??

thanks
SELECT noticias.notId,noticias.notTitular,noticias.notFuente,noticias.notEntradilla,noticias.notDescripcion,noticias.notTxtImg,noticias.notVisible,noticias.notDestacado,noticias.notFecha,imagenes.imgElemento,imagenes.imgElementoId,imagenes.imgArchivo,imagenes.imgElemento,imagenes.imgElementoId,imagenes.imgArchivo FROM noticias Left Join imagenes ON noticias.notId = imagenes.imgElementoId WHERE noticias.notId IN (SELECT  *  from imagenes where imgElemento='Noticias' limit 0,1)  and noticias.notDestacado=0 and notVisible=1   ORDER BY notFecha ASC

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
leakim971Multitechnician
Top Expert 2014

Commented:
Hello nkoriginal,

A look to the manual : http://dev.mysql.com/doc/refman/5.0/en/subquery-errors.html

Regards.
leakim971Multitechnician
Top Expert 2014

Commented:
How many record << SELECT  *  from imagenes where imgElemento='Noticias' >> return ?
HainKurtSr. System Analyst

Commented:
it is hard to understand... i changed this part...

WHERE n.notId IN (SELECT imgElementoId

is this what you want...
SELECT n.notId,n.notTitular,n.notFuente,n.notEntradilla,n.notDescripcion,n.notTxtImg,n.notVisible,n.notDestacado,n.notFecha,
       i.imgElemento,i.imgElementoId,i.imgArchivo,i.imgElemento,i.imgElementoId,i.imgArchivo 
  FROM noticias n Left Join imagenes i ON n.notId = i.imgElementoId 
 WHERE n.notId IN (SELECT imgElementoId from imagenes i2 where imgElemento='Noticias' limit 0,1)
   AND n.notDestacado=0 and notVisible=1
 ORDER BY notFecha ASC

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

please try this:

SELECT noticias.notId,noticias.notTitular,noticias.notFuente,noticias.notEntradilla,noticias.notDescripcion,noticias.notTxtImg,noticias.notVisible,noticias.notDestacado,noticias.notFecha,imagenes.imgElemento,imagenes.imgElementoId,imagenes.imgArchivo,imagenes.imgElemento,imagenes.imgElementoId,imagenes.imgArchivo FROM noticias Left Join imagenes ON noticias.notId = imagenes.imgElementoId and imgElemento='Noticias'
WHERE  noticias.notDestacado=0 and notVisible=1   
ORDER BY notFecha ASC
Limit 1;

Open in new window

HainKurtSr. System Analyst

Commented:
and query looks logically wrong... can you please explain the table structures and what are you trying to get?
Sr. System Analyst
Commented:
looks like there is no need for subquery after join...
SELECT n.notId,n.notTitular,n.notFuente,n.notEntradilla,n.notDescripcion,n.notTxtImg,n.notVisible,n.notDestacado,n.notFecha,
       i.imgElemento,i.imgElementoId,i.imgArchivo,i.imgElemento,i.imgElementoId,i.imgArchivo 
  FROM noticias n Left Join imagenes i ON n.notId = i.imgElementoId 
 WHERE i.imgElemento='Noticias'
   AND n.notDestacado=0 and notVisible=1
 ORDER BY notFecha ASC

Open in new window

leakim971Multitechnician
Top Expert 2014

Commented:
Try this :


SELECT noticias.notId,noticias.notTitular,noticias.notFuente,noticias.notEntradilla,noticias.notDescripcion,noticias.notTxtImg,noticias.notVisible,noticias.notDestacado,noticias.notFecha,imagenes.imgElemento,imagenes.imgElementoId,imagenes.imgArchivo,imagenes.imgElemento,imagenes.imgElementoId,imagenes.imgArchivo
FROM noticias, imagenes, (SELECT imgElementoId  from imagenes where imgElemento='Noticias' limit 0,1) i
WHERE noticias.notId = imagenes.imgElementoId AND noticias.notId = i.imgElementoId and noticias.notDestacado=0 and notVisible=1
ORDER BY notFecha ASC

Open in new window

Author

Commented:
thanks to all people for all comments.
Hainkurt it was right I dont need the subquery.
I changed my statment for this.

SELECT noticias.notId,noticias.notTitular,noticias.notFuente,noticias.notEntradilla,noticias.notDescripcion,noticias.notTxtImg,noticias.notVisible,noticias.notDestacado,noticias.notFecha,imagenes.imgId,imagenes.imgElemento,imagenes.imgElementoId,imagenes.imgArchivo,imagenes.imgAlt, MAX(imgElementoId) as ImageID FROM noticias left Join imagenes ON noticias.notId = imagenes.imgElementoId  where notDestacado=0 and notVisible=1 GROUP BY noticias.notId ORDER BY notTitular ASC

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial