Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    11

    Red face Unanswered: Subqueryes .. Just Not Work

    EVERY TIME I USE A SUBQUERY MYSQL SERVER LOCK A TABLE AND RETURN NO RESULT
    IT LOCK IT SO ...HARD.. THEN I HAVE NO ACCES TO THAT TABLE UNTIL SERVER RESTART,EVEN FOR A SIMPLE SELECT QUERY

    THE QUERY WHO FLOOWS WORKS FINE UNTIL I ADDED THE SUBQUERY
    THE SUBQUERY ALONE RETURN 2 ROWS ...
    THE MAIN QUERY ALONE RETURN 2883 ROWS AND THE VALUES
    CONTAIN THE SUBQUERY VALUES SO IT SHOULD RETURN 2 ROWS

    SELECT ANEXEPENALITATI.CODCLIENT,NRFACTURA,MAX(ANEXEPENAL ITATI.DATA) FROM FACTURA.ANEXEPENALITATI INNER JOIN FACTURA.FACTURI ON FACTURI.NRFACT=ANEXEPENALITATI.NRFACTURA WHERE TIP=1 AND SOLDFACTURA <>0 AND ANEXEPENALITATI.CODCLIENT IN(SELECT CODCLIENT FROM ANEXEFACTURARE WHERE GENERAT='n') GROUP BY NRFACTURA

    I HAVE MYSQL VER 4.1 NEWLY UPDATED (2 DAYS AGO) RUNNING ON LINUX RED HAT 7.3

    IN FACT ANY SUBQUERY NOT WORK ...
    ANYBODY KNOW WHY ? A HINT ???

  2. #2
    Join Date
    Oct 2003
    Posts
    63

  3. #3
    Join Date
    Nov 2003
    Posts
    11
    TABLE ANEXEPENALITATI
    FIELDS
    ID PK INT 14
    CODCLIENT VARCHAR 8 INDEX
    NRFACTURA INT 14 INDEX
    NRFACTGENERATA IN 14
    GENERAT VARCHAR 1
    DATA DATE INDEX
    TIP INT 14

    TABLE FACTURI
    FIELDS
    NRFACT PK INT 14
    SOLDFACTURA DOUBLE 14,2
    DATA DATE INDEX
    CODCLIENT VARCHAR 8 INDEX

    TABLE ANEXEFACTURARE
    FIELDS
    DATA DATE INDEX
    NRCITIRE PK INT 14
    CODCLIENT VARCHAR 8 INDEX

    ANY HOPE FOR HELP ?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rewrite the subquery as a join
    Code:
    SELECT ANEXEPENALITATI.CODCLIENT
         , NRFACTURA
         , MAX(ANEXEPENALITATI.DATA) 
      FROM FACTURA.ANEXEPENALITATI 
    INNER
      JOIN ANEXEFACTURARE 
        ON ANEXEPENALITATI.CODCLIENT = ANEXEFACTURARE.CODCLIENT
    INNER
      JOIN FACTURA.FACTURI 
        ON FACTURI.NRFACT=ANEXEPENALITATI.NRFACTURA 
     WHERE TIP=1 
       AND SOLDFACTURA <>0 
       AND ANEXEFACTURARE.GENERAT='n'
    GROUP
        BY ANEXEPENALITATI.CODCLIENT
         , NRFACTURA
    rudy
    http://r937.com/

  5. #5
    Join Date
    Nov 2003
    Posts
    11
    thank-you very much ..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •