SQL SELECT Joins VS. the WHERE clause


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: SQL SELECT Joins VS. the WHERE clause

  1. #1
    Al Lambert Guest

    SQL SELECT Joins VS. the WHERE clause

    I have backed into learning SQL by necessity. So please be gentle, and don't
    assume I know anything. Our ERP is in VFP and in trying to write
    multi-table queries I became aware of the designers were just visually
    mapping an SQL statement. I am still having trouble with Joins on more than
    two tables. In writing my own statements I have better results using the
    WHERE clause than I do with trying to join tables. However, I feel I must
    be missing something, so here comes the first stupid newbie question. What
    is the advantage of Joins over declarations in the Where clause?

    Our system has a APMAST table with Vendor, invoice number, and date
    information. APITEM is a child with multiple part number line items for
    each invoice. The only field in APITEM that relates to APMAST is .fcinvkey
    that is a concatenation of the Vendor & invoice number from APMAST. Most of
    the reports in the system itself must use a Tag/Index relationship, because
    the DBC doesn't seem to have any relationships (joins?) set up that would
    help a rookie like me. Anyway the following crude Select statement worked
    after many attempts at joins did not. I shouldn't complain as I got the
    correct results. But, I have discovered I really like this SQL stuff and do
    not want to stop learning just because its challenging or I might look
    stupid. How would one of the VFP Illuminati do it? (Please forgive my
    obvious cut an paste technique) This executes in MsQuery trough ODBC.

    SELECT
    APMAST.FVENDNO+APMAST.FCINVOICE,
    apmast.finvdate,
    apitem.fvpartno,
    apitem.frev,
    apitem.fmdescr,
    apitem.fnunit,
    apitem.fnexpcost,
    apitem.fcinvkey

    FROM apitem apitem, apmast apmast

    WHERE (APMAST.FVENDNO+APMAST.FCINVOICE=apitem.fcinvkey) AND
    (apitem.fvpartno='1651421-D')

    Order By apmast.finvdate



  2. #2
    Cindy Winegarden Guest

    Re: SQL SELECT Joins VS. the WHERE clause

    Hi Al,

    You'll like SQL when you get going with it. A good book on SQL is "SQL For
    Smarties" Second Edition by Joe Celko. ISBN 1-55860-576-2.

    Like other platforms, FoxPro has its own subset/superset of ANSI SQL-92.

    Earlier versions of FoxPro did not support the JOIN syntax and some SQL
    database don't support it either. The difference between the JOIN syntax and
    the WHERE shows up in the OUTER JOINS which will show NULLs in fields where
    there are not matching child records.

    Most of what I know I've learned from Anders Altberg, so be sure to read all
    of his posts wherever you can find them (the MS newsgroups)

    Here's how to write your SQL with the JOIN syntax:

    SELECT ;
    ApMast.fVendNo + ApMast.fcInvoice AS ApMastFcInvKey, ;
    ApMast.finvdate, ;
    ApItem.fvpartno, ;
    ApItem.frev, ;
    ApItem.fmdescr, ;
    ApItem.fnunit, ;
    ApItem.fnexpcost, ;
    ApItem.fcinvkey ;
    FROM ApMast INNER JOIN ApItem ;
    ON ApMast.fVendNo + ApMast.fcInvoice = ApItem.fcinvkey ;
    AND ApItem.fvpartno='1651421-D' ;
    INTO CURSOR Temp ;
    Order By ApMast.finvdate

    This gives Master and Item detail for invoices where part '1651421-D' was
    purchased.

    Now, to get Vendors and their invoices you do:

    SELECT ApMast.* ApInv.* ;
    FROM ApMast LEFT JOIN ApInv ;
    ON ApMast ApMast.fVendNo + ApMast.fcInvoice = ApItem.fcinvkey ;
    ......

    You will get NULLs in the ApInv fields for Master Vendors who did not buy
    any parts.

    Post back with more questions!


    --


    Cindy Winegarden

    Microsoft Certified Professional, Visual FoxPro
    Microsoft Visual FoxPro MVP

    Duke Children's Information Systems
    Duke University Medical Center
    cindyw@duke.edu



    "Al Lambert" <alambert@mertzok.com> wrote in message
    news:39f8e2d3@news.devx.com...
    | I have backed into learning SQL by necessity. So please be gentle, and
    don't
    | assume I know anything. Our ERP is in VFP and in trying to write
    | multi-table queries I became aware of the designers were just visually
    | mapping an SQL statement. I am still having trouble with Joins on more
    than
    | two tables. In writing my own statements I have better results using the
    | WHERE clause than I do with trying to join tables. However, I feel I must
    | be missing something, so here comes the first stupid newbie question.
    What
    | is the advantage of Joins over declarations in the Where clause?
    |
    | Our system has a APMAST table with Vendor, invoice number, and date
    | information. APITEM is a child with multiple part number line items for
    | each invoice. The only field in APITEM that relates to APMAST is
    ..fcinvkey
    | that is a concatenation of the Vendor & invoice number from APMAST. Most
    of
    | the reports in the system itself must use a Tag/Index relationship,
    because
    | the DBC doesn't seem to have any relationships (joins?) set up that would
    | help a rookie like me. Anyway the following crude Select statement worked
    | after many attempts at joins did not. I shouldn't complain as I got the
    | correct results. But, I have discovered I really like this SQL stuff and
    do
    | not want to stop learning just because its challenging or I might look
    | stupid. How would one of the VFP Illuminati do it? (Please forgive my
    | obvious cut an paste technique) This executes in MsQuery trough ODBC.
    |
    | SELECT
    | APMAST.FVENDNO+APMAST.FCINVOICE,
    | apmast.finvdate,
    | apitem.fvpartno,
    | apitem.frev,
    | apitem.fmdescr,
    | apitem.fnunit,
    | apitem.fnexpcost,
    | apitem.fcinvkey
    |
    | FROM apitem apitem, apmast apmast
    |
    | WHERE (APMAST.FVENDNO+APMAST.FCINVOICE=apitem.fcinvkey) AND
    | (apitem.fvpartno='1651421-D')
    |
    | Order By apmast.finvdate
    |
    |



  3. #3
    Barbara Peisch Guest

    Re: SQL SELECT Joins VS. the WHERE clause

    Cindy's done a good job in showing you how to use JOINs. One of the things
    I really like about using JOIN instead of joining tables in the WHERE clause
    is it separates your joins from your filters, which makes things easier to
    decipher.

    You may have also noticed that Cindy used the AS clause after the SELECT
    APMAST.FVENDNO+APMAST.FCINVOICE clause. That's because when you use an
    expression in SELECT, you need the AS clause to tell VFP what to call the
    field in the result. Otherwise, you can't be sure what its name is when you
    want to reference it later.

    --


    -BP
    www.peisch.com




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center