September 12, 2009 at 12:03 pm
Having come from Oracle using (+) for outer joins, I'm trying to implement a query using joins. In the following query, the Client table may have IDs from the other tables or they could be NULL. For example a client's salesperson and e-mail address may be null and can only be filled if there are appropriate IDs in those tables. So for the following query, I get a "Syntax error in JOIN operation" error and it points to the bolded part below. I guess I really don't understand how join clauses are supposed to work, whether they should be LEFT or RIGHT and if the order of the tables matters. Any help on this or direction to examples on how to join more than ttwo tables is genuinely appreciated. I've found a few examples but they don't quite fit what I'm trying to do. Thanks for helping a complete SQL JOIN newbie.
SELECT c.ClientId, c.FirstName, c.MiddleName, c.LastName, c.LegalName, a.StreetAddress, a.StreetDirection, a.City, a.State, a.ZipCode, a.Country, c.WorkPhone, c.Extension, c.HomePhone, c.CellPhone, c.HomeFax, c.Comments, c.AddToMailList, c.Occupant, e.EmailAddress, c.BirthDate, s1.FirstName, s1.LastName, s2.FirstName, s2.LastName
FROM Client c LEFT JOIN (Address a LEFT JOIN (Email e RIGHT JOIN (SalesPerson s1 RIGHT JOIN (SalesPerson s2 ON c.SalesPerson2Id = s2.SalesPersonId) ON c.SalesPerson1Id = s1.SalesPersonId) ON c.EmailAddressId = e.EmailAddressId) ON c.MailingAddressId = a.AddressId ORDER BY c.ClientId
September 12, 2009 at 1:42 pm
Hi (and welcome at SSC 🙂 )
Two things I changed.
* Don't use nested JOINs when they are not needed. I moved your nested joins up to the FROM clause.
* Keep the braces away (the reason for your error message)
Try this:
SELECT
c.ClientId
,c.FirstName
,c.MiddleName
,c.LastName
,c.LegalName
,a.StreetAddress
,a.StreetDirection
,a.City
,a.State
,a.ZipCode
,a.Country
,c.WorkPhone
,c.Extension
,c.HomePhone
,c.CellPhone
,c.HomeFax
,c.Comments
,c.AddToMailList
,c.Occupant
,e.EmailAddress
,c.BirthDate
,s1.FirstName
,s1.LastName
,s2.FirstName
,s2.LastName
FROM Client c
LEFT JOIN Address a ON c.MailingAddressId = a.AddressId
-- You used a RIGHT JOIN, probably you need a LEFT/INNER JOIN here
LEFT JOIN SalesPerson s1 ON c.SalesPerson1Id = s1.SalesPersonId
-- You used a RIGHT JOIN, probably you need a LEFT/INNER JOIN here
LEFT JOIN SalesPerson s2 ON c.SalesPerson2Id = s2.SalesPersonId
LEFT JOIN Email e ON c.EmailAddressId = e.EmailAddressId
ORDER BY c.ClientId
Greets
Flo
September 13, 2009 at 12:11 am
Unlearning the (+) syntax is painful at first. The SQL-92 syntax is rather easier to use and read, once you become familiar with it.
The following link to the official documentation is a good introduction:
http://msdn.microsoft.com/en-us/library/ms187518(SQL.90).aspx
In particular, it explains that the order of tables in the FROM clause is important for LEFT and RIGHT joins.
I would echo Flo's suggestion to avoid using parentheses with the SQL-92 syntax, except in those circumstances that demand their use. That is an advanced topic, so I'll leave that for another time.
Paul
September 13, 2009 at 9:36 am
Thanks so much for your input. It does look a lot easier when it's written the way you did it. I'll be sure to have a look at the link from Paul's post too, to get a better handle on JOINs.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply