Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2012
    Posts
    91

    Unanswered: Is there simpler query

    I came up with the algorithm in order to obtain specific data. I think this query too complex. Is there simpler query?
    "SELECT ZID FROM IDZZZ INNER JOIN SKLAD ON IDZZZ.OPIS=SKLAD.ID ";
    WHERE IDZZZ.ZZZ=(...)
    while( !SQLQuery1->Eof )
    {
    "SELECT IDCN.CNID FROM IDZZZ INNER JOIN IDCN ON IDZZZ.ZID=IDCN.ZID WHERE IDZZZ.ZID= SQLQuery1->FieldByName("ZID")->AsString"
    while( !SQLQuery2->Eof )
    {
    "SELECT WAR FROM IDCN "INNER JOIN (...)"
    ...........
    "WHERE IDCN.CNID='" + SQLQuery2->FieldByName("CNID")->AsString + "'"
    while( !SQLQuery3->Eof )
    {
    }
    }
    }

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by duf View Post
    Is there simpler query?
    possibly

    would you mind showing all the sql?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2012
    Posts
    91
    Code:
    String c = "SELECT ZID, _ID, OGLMRN, DATAOPER, MASABR FROM IDZZZ ";
    c = c + "INNER JOIN SKLAD ";                                
    c = c + "ON IDZZZ._ID_OPIS=SKLAD._ID ";
    c = c + "WHERE IDZZZ.ZZZ='" + Form1->_SMRNOGL + "' ORDER BY DATAOPER ASC";
    SQLQuery1->CommandText = c;
    SQLQuery1->Open();
    while( !SQLQuery1->Eof )
    {
      c = "SELECT IDCN.CNID, IDCN.CN FROM IDZZZ "; 
      c = c + "INNER JOIN IDCN "; 				
      c = c + "ON IDZZZ.ZID=IDCN.ZID ";
      c = c + "WHERE IDZZZ.ZID='" + SQLQuery1->FieldByName("ZID")->AsString + "'";
      SQLQuery2->CommandText = c;
      SQLQuery2->Open();
      while( !SQLQuery2->Eof )
      {
          String c = "SELECT WAR, SUM(ILOSC), NETTO FROM IDCN ";
          c = c + "INNER JOIN IDWARTOSC ";
          c = c + "ON IDCN.CNID=IDWARTOSC.CNID ";
          c = c + "INNER JOIN IDNETTO ";
          c = c + "ON IDCN.CNID=IDNETTO.CNID ";
          c = c + "INNER JOIN IDOP ";
          c = c + "ON IDCN.CNID=IDOP.CNID ";
          c = c + "WHERE IDCN.CNID='" + SQLQuery2->FieldByName("CNID")->AsString + "'";
          SQLQuery3->CommandText = c;
          SQLQuery3->Open();
    			...
          SQLQuery3->Close();
    
          SQLQuery2->Next();
      }
      SQLQuery2->Close();
       
      SQLQuery1->Next();
    }
    SQLQuery1->Close();

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT war
         , SUM(ilosc)
         , netto
      FROM idcn
    INNER
      JOIN idwartosc
        ON idcn.cnid=idwartosc.cnid
    INNER
      JOIN idnetto
        ON idcn.cnid=idnetto.cnid
    INNER
      JOIN idop
        ON idcn.cnid=idop.cnid
    INNER
      JOIN ( SELECT idcn.cnid
               FROM idzzz
             INNER
               JOIN idcn
                 ON idcn.zid = idzzz.zid
             INNER
               JOIN ( SELECT zid
                        FROM idzzz
                      INNER
                        JOIN sklad
                          ON sklad._id = idzzz._id_opis
                       WHERE idzzz.zzz = '" + Form1->_SMRNOGL + "' ) AS query1
                 ON query1.zid = idzzz.zid ) AS query2
       ON query2.cnid = idcn.cnid
    GROUP
        BY war
         , netto
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2012
    Posts
    91
    It seems to me a very complicated query. I think it is difficult to understand over time what it has to do. Sure it run faster than what I presented.

Posting Permissions

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