JOIN in SQL statements

[Ian Docksey]

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."

[Michael Mattias]

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

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, [b]Transation[/b].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):

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

[Peter Jinks]

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:

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.

[Martin Draper]

Try LEFT JOIN instead of just join

[Ian Docksey]

Thanks everyone
Alias very useful