Announcement

Collapse
No announcement yet.

JOIN in SQL statements

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • JOIN in SQL statements

    I have been trying to JOIN 3 tables together without success.
    The line beginning with AND is the offending line
    Can someone please let me know what it should be

    Many Thanks
    Ian

    sSQLStatement = _
    "SELECT Contacts.PostalName, Contacts.CompanyName, Addresses.Position, "+ _
    "Addresses.Department, Addresses.Address1, Addresses.Address2, "+ _
    "Addresses.Address3, Addresses.PostCode, Addresses.Town, Addresses.County, "+ _
    "Addresses.Country, Contacts.ContactID, Transations.DeliveryAddress " + _
    "FROM Contacts JOIN Addresses ON Contacts.AddressID=Addresses.AddressID " + _
    "AND Contacts JOIN Transactions ON Contacts.ContactID=Transactions.ContactID " + _
    "WHERE Transactions.PaymentSecondRef LIKE '"+TBSNo+"' " + _
    "AND Addresses.DefaultAddress = 1"
    I am using SQL Tools with some success BUT in their help file there is no help for the JOIN option
    The following 2 paragraphs come from their help file!
    "You may also notice that certain relatively common syntax elements are not included here, such as the SELECT statement's GROUP BY, HAVING, UNION and JOIN clauses. Again, these keywords are not part of the minimum ODBC syntax, and some ODBC drivers may not support them, so they are not covered here.
    For complete information about the syntax that your ODBC driver accepts, you will need to acquire additional, driver-specific reference materials."
    Ian Docksey, Trinitarian Bible Society

  • #2
    I generally eschew explicit joins and let the DBMS handle it by using WHEREs...

    Code:
    sSQLStatement = _
    "SELECT Contacts.PostalName, Contacts.CompanyName, Addresses.Position, "+ _
    "Addresses.Department, Addresses.Address1, Addresses.Address2, "+ _
    "Addresses.Address3, Addresses.PostCode, Addresses.Town, Addresses.County, "+ _
    "Addresses.Country, Contacts.ContactID, Transation.DeliveryAddress " + _
    "FROM Contacts, Addresses,Transactions " _
    & " WHERE contacts.AddressID=ADdresses.AddressID " _
    &  "and contacts.contactID=Transactions.contactID " _
    &  "and Transactions.PaymentSecondRef LIKE 'TBSNo'" _
    &  "and addresses.defaultAddress=1 "

    (note one of your table names appears incorrect earlier in statement)

    Note also that aliases would make this a lot easier to type ( and reduce mispelling of table names):
    Code:
    sSQLStatement = _
    "SELECT C.PostalName, C.CompanyName, A.Position, "+ _
    "A.Department, A.Address1, A.Address2, "+ _
    "A.Address3, A.PostCode, A.Town, A.County, "+ _
    "A.Country, C.ContactID, T.DeliveryAddress " + _
    "FROM Contacts C, Addresses A,Transactions T " _
    & " WHERE c.AddressID=A.AddressID " _
    &  "and c.contactID=T.contactID " _
    &  "and T.PaymentSecondRef LIKE 'TBSNo'" _
    &  "and a.defaultAddress=1 "
    BTW:
    "and T.PaymentSecondRef LIKE 'TBSNo'" could be and T.PaymentSecondRef ='TBSNo'" since that's how LIKE works with no "%" in the token.


    MCM
    Last edited by Michael Mattias; 26 Oct 2007, 08:49 AM.
    Michael Mattias
    Tal Systems Inc. (retired)
    Racine WI USA
    [email protected]
    http://www.talsystems.com

    Comment


    • #3
      Hi Ian,

      If you want to keep the JOIN syntax, then just remove the "AND Contacts" from the beginning of the offending line and it should work:

      Code:
      sSQLStatement = _
      "SELECT Contacts.PostalName, Contacts.CompanyName, Addresses.Position, "+ _
      "Addresses.Department, Addresses.Address1, Addresses.Address2, "+ _
      "Addresses.Address3, Addresses.PostCode, Addresses.Town, Addresses.County, "+ _
      "Addresses.Country, Contacts.ContactID, Transactions.DeliveryAddress " + _
      "FROM Contacts JOIN Addresses ON Contacts.AddressID=Addresses.AddressID " + _
      "JOIN Transactions ON Contacts.ContactID=Transactions.ContactID " + _
      "WHERE Transactions.PaymentSecondRef LIKE '"+TBSNo+"' " + _
      "AND Addresses.DefaultAddress = 1"
      I would also second Michael's suggestion that you use aliases - they do make long statements like this much easier to read (not to mention saving on the typing).

      Regards,

      Pete.

      Comment


      • #4
        Try LEFT JOIN instead of just join

        Comment


        • #5
          Thanks everyone
          Alias very useful
          Ian Docksey, Trinitarian Bible Society

          Comment

          Working...
          X